Mortgage calculator help - and Excel spreadsheets

Kuztardd

deleted thread

cheveleyaprkpaul

Hi

The Excel functions you want to use are PMT (This is your total monthy payment, interest plus principal), PPMT (This is the amout of principal you pay off) and IPMT (The amount of interest). Obviously the results of PPMT and IPMT should add up to PMT, except if there is a penny or two rounding error.

You can use these to construct a spreadsheet which will show how your debt decreases as you make payments. Its quite interesting to watch the split of principal and interest change over the life of the loan... but then I am a nerd.

You have to use a minus sign at the start to get the sums to come out right.

So for example in my spreadsheet the formula for the monthly payment is
=-PMT(D2/12,156,110000,0,0) where D2 has the interest rate applicable.

The figure for the loan amount must stay fixed, even when the balance goes down for it to work. THe examples on the Microsoft website are useful - search for help on these functions and you should find it.

Have fun!

antoniomontana

Kuztardd wrote: »

along with your reply, ive also found this little jem:
http://www.wikihow.com/Create-a-Mortgage-Calculator-With-Microsoft-Excel

great link, but do you know how you would change it so an interest rate change would be reflected only from the month it happened instead of the whole spreadsheet

KTF

There are also other examples here: http://www.mortgagesexposed.com/Book_Contents/spreadsheet_summary.htm