Mortgage spreadsheets

[Mattzlaff]

Hey there everyone, been doing a lot of good moves lately with my spare cash. Tax sheltered accounts are in order and I am looking to put the rest of my excess savings towards my only debt - the mortgage.

However I want to look at the impact of putting additional payments on the principle and what happens to the interest payments following. So I am looking for some suggestions on where to find helpful spreadsheets that I could use to see this info.

Any help is appreciated.

[Poundwise]

I like this one from Khan Academy.
https://cdn.kastatic.org/downloads/MortgageCalculator.xlsx

Change the cell values in the table at upper left to your own values. To see the effect of an additional payment, adjust the numbers in the "Payment" column at right for the correct month.

[MDM]

From http://forum.mrmoneymustache.com/mustachianism-around-the-web/amortization-schedule-calculator/msg1219175/#msg1219175:

1. Open a new sheet in Excel
2. Press the "sheet1" tab on the bottom with a right-click
3. "Insert" --> "Spreadsheet solutions" --> "Loan Amortization"

[dandarc]

Holy cow MDM - I've done that by hand so many times.  Of course I'm a nerd that likes making spreadsheets.

Have you looked at the "Personal Monthly Budget" one as well?  Seems pretty thorough. 

[solon]

That's an OK amortization spreadsheet, but it doesn't allow any flexibility with the extra payment.

I want to enter a different extra amount for each month. Or delay starting my extra payments for a year, and see what that does to the payoff date. Or enter what my actual extra amount was for each month.

Does anyone have a sheet that allows for that?

[dandarc]

That's an OK amortization spreadsheet, but it doesn't allow any flexibility with the extra payment.

I want to enter a different extra amount for each month. Or delay starting my extra payments for a year, and see what that does to the payoff date. Or enter what my actual extra amount was for each month.

Does anyone have a sheet that allows for that?

Right click the tab, and click "unprotect sheet".  Now you can enter whatever you want in the "extra payment" column.

ETA - assuming you're referring to the Excel built-in one.

[Mattzlaff]

Poundwise that link works perfect for my needs thanks so much!

That's an OK amortization spreadsheet, but it doesn't allow any flexibility with the extra payment.

I want to enter a different extra amount for each month. Or delay starting my extra payments for a year, and see what that does to the payoff date. Or enter what my actual extra amount was for each month.

Does anyone have a sheet that allows for that?

The sheet that Poundwise sent I figured out you could put a monthly payment in the payment column that would change the rest of the spread sheet. For me the spread sheet column for payment was column "K" so for example if on the 24th month I wanted to add 200$/mo I could simply enter +200 to the formula in K24 for and the following rows in the column would take that and the spread sheet would update accordingly. However to add a one time payment to the payment columns all of the rest would update to take the payment amount when you added say +10000 to the formula for the month you wanted to add a payment to. To get around this say I added +1000 to K4 I would need to change the formula for K5 to K3.

Hope this makes sense I had a bit of time to play with it while I'm at work hehe. So give it a try it may work for you with some fooling around.

[MDM]

Holy cow MDM - I've done that by hand so many times.  Of course I'm a nerd that likes making spreadsheets.

Have you looked at the "Personal Monthly Budget" one as well?  Seems pretty thorough.

Does seem thorough.  Interesting how much stuff is pre-loaded into Excel but most of us never see it.