Home mortgage amortization schedule Excel

You can use this little customized "application" in excel. You only have to make a few changes and change the number of rows for the number of years of payments:

enter image description here

In this example, I used a Principal amount of $ 2, 000, 000, rate of 11% and Period of 20 years with equal installments.

The interest charges for each year and the payment is displayed.

In the first line, there are specific formulas:

E2: =$B$2

F2: =E2*$B$3

G2: =-PMT($B$3, $B$4-D2+1, E2, 0, 0) (This is the 'meat' of the application)

H2: =E2+F2-G2

The second row:

E2: =H2

F2: =E3*$B$3

G2: =-PMT($B$3, $B$4-D3+1, E3, 0, 0)

H2: =E3+F3-G3

At this stage, you can drag the 4 formulas down till the last year of payment.

You can of course manually put a value in the column Payment and the interest and subsequent payments will immediately be recalculated. I think this is what you were looking for. If the number of periods of payments (years) changes, then this cannot help you unless it is revised completely (have to take into account payments already made and charges accrued already, potential penalties and so on).

You can get a copy of the workbook in the picture through and toy a bit with it I guess.

The PMT formula:

It takes 3 to 5 parameters:

1. The interest rate for the period.

Since we're having 11% for 1 year, leave it as such. If the payment was on a monthly basis, you could change it to Int/12. Of course, the number of periods will change. I would advise only changing the interest rate to a monthly interest rate and re-calculate a more appropriate loan period (for monthly installment, change 11% to =0.11/12 and change the period to =20*12)

2. The number of period until the loan is fully repaid.

This is 20 in the example. If you have monthly payments, you will need to change this to `20*12 and change the interest rate accordingly as mentioned above. It won't anymore be strictly 'years' then, but periods until full repayment of loan.

3. The present value of the loan.

This is the loan you took. Shouldn't be difficult. Now in the spreadsheet, I made this value dynamic so that it changes each time the net balance changes, hence can accommodate a change in payment and still recalculate the subsequent interests and payments.

4. Fair value of loan (optional, defaults to 0).

Put a value there if the loan at the end has a fair value. Usually, you pay the totality of the loan by the end of the periods, so it'll be zero, but if for example, after 2 years, the loan value has to be $100, 000, put 100000 here.

5. Type of loan (optional, defaults to 0).

There are two types of loans the pmt formula recognises:

  1. Loans where payments are made at the beginning of the month (value 1),
  2. Loans where payments are made at the end of the month (value 0).

Prepayment insight: Analysis of Option One Mortgage Corporation's hybrid adjustable rate mortgage home equity loan portfolio
Book (Banc One Capital Markets)


2009-08-07 08:00:46 by spponfedbs

Peter Schiff: This is just getting started. It's not just subprimes. This is a problem for the entire mortgage industry. It's not just people with bad credit that committed to mortgages they couldn't afford. It's not just people with bad credit who are going to see their home equity vanish... This is going to be an enormous credit crunch...
Neil Cavuto: You must be a laugh-riot at parties.
Ben Stein: ...subprime is tiny. Subprime is a tiny, tiny blip.
Peter Schiff: It's not tiny. And again, it's not just subprime

You might also like:

FREE Excel Amortization …
FREE Excel Amortization …
Amortization Schedule for Excel …
Amortization Schedule for Excel …

Protective Life Corp. (PL): Protective Life Corporation Hosts 2013 Investor ..  — Seeking Alpha
We just don't do table shaving programs. We don't make business ....

Related posts:

  • Avatar Taylor How do you calculate a mortgage plan in excel given the following details?
    Sep 06, 2013 by Taylor | Posted in Software

    Monthly mortgage payment assuming a $250, loan, 30-year repayment schedule, and a 6% interest rate. Determine your “net” payment taking into account the Federal income tax deduction for home mortgage interest , assuming that you are in the 25% tax bracket. Show all 360 months running horizontally, across the top of the page, from left to right, with line labels in the left hand column. Include lines for monthly beginning balance, total monthly payment, monthly principal payment, monthly interest payment, monthly tax deduction value, net monthly payment (net of actual payment less monthly tax deduction …

    • See the above link . You download the same and use it according to your needs. it is a template, that you could download and install. Once installed, you can use it by opening this and supply your data. I hope it helps. There are plenty of templates microsoft supplies. See them also if it suits you