[Excel help] Mortgage calculations

[JTsyo]

I was trying to setup a spreadsheet to see how much money could be saved if the monthly payments were changed to include additional principal payments. I'm not sure how they arrive at the monthly rate but if that's a given how do you you set it up to do the rest?
If there's already an online calculator that odes this, can someone point me to it?

 

[D1gger]

If you want to PM me, I can email you an excel spreadsheet I wrote for this express purpose.

 

[Jadow]

ditto I have one with an amortization schedule you can edit the inital amount, payments, and interest rate on.

Also have an investment tracker that works very similar

 

[JTsyo]

PM'd

 

[Mike Gayner]

It takes like 2 minutes to make an amortisation schedule - all you need is the maths education of a 12 year old. Interest is the same every month ((rate x principal outstanding) / 12) so your principal payment is the difference between your payment and the interest.

 

[Jaepheth]

Are payments due, or immediate?
How is the interest described? (Often is 'Nominal' meaning you divide that number by 12 to get monthly interest, but if it's 'effective' then you take the 12th root to get monthly interest)

 

[highland145]

http://www.vertex42.com/

Free with extra payments.

I use it for a split on the heloc. Wife and bro in law bought some land. Added it to the $35K on the loan.

 

[JTsyo]

thanks. Those extra payments are huge on the long term. When I was looking at it at first I forgot to take into account that the monthly payments stay the same while the interest payments drop off so you're paying off larger chucks off the principal at the end.

 

[alkemyst]

Excel is easy =pmt(rate,periods,amount). Your rate is monthly as is period, so take your apr/12. A 30 year loan is 360 periods

you have to figure out what your escrows (tax/insurance) are though to get what the real payment will be, but a simple amortization will show you your accelerated payoff regardless of what's tacked on.

There are a ton of calculations at www.dinkytown.com that can do this with just plugging in the numbers.

http://www.dinkytown.com/java/MortgagePayoff.html

 

[Cal166]

http://www.vertex42.com/

Free with extra payments.

I use it for a split on the heloc. Wife and bro in law bought some land. Added it to the $35K on the loan.

I used this one as well but there's no option to add in your property tax. So the monthly mortgage payment is only towards the bank and does not account for the extra few hundred a month towards property taxes.

 

[highland145]

I used this one as well but there's no option to add in your property tax. So the monthly mortgage payment is only towards the bank and does not account for the extra few hundred a month towards property taxes.

Sorry. Didn't take that into consideration since it's for the heloc. But they are pretty cool,imo.

 

[rcpratt]

I can't upload a file at work, but I had been working on something similar and you just inspired me to finish it. Shoot me a PM if it looks useful for you. It's a little sloppy but works. The macro will run when you save the workbook.

 

[Cal166]

I can't upload a file at work, but I had been working on something similar and you just inspired me to finish it. Shoot me a PM if it looks useful for you. It's a little sloppy but works. The macro will run when you save the workbook.

Looks good! Can add a line under mortgage amount for someone to put in your Property tax for the year and then divided by 12 and add it to your mortgage payment.