For some time, I’ve been visiting online “mortgage calculators” and I’ve been trying to work out the formulae they use. For instance, if you borrow $100,000 over 25 years – how they calculate the repayments.
Even more complex – if you increase the minimum repayment, how much would the total term be reduced.
That kind of thing! Anyone care to share their spreadsheets? If yes, please send them here:
[email protected] (please change “inch” to “foot” after clicking).
Allan.
Have a look at wespacs calculators… like it because you can quickly play around with the figures and change the outcome.. i.e quickly see that if you increase payments or decrease loan amount slightly, how it effects the total.
Hi Allan
Try the site listed below.
Regards
Allan,
I hope you got my e-mail. I just discovered that in Excel, if you choose Tools/Protection from the toolbar, you can Unprotect the spreadsheet and view the formulas within. (In case you hadn’t already found out).
For anyone else reading this, the spreadsheet I am referring to is the “Loan Amortization 1.xls” spreadsheet that comes with Office XP. To access it click on New Office Document and go to the Spreadsheet Solutions tab.
Cheers
Karl
[
]The future belongs to those who believe in the beauty of their dreams. – Eleanor Roosevelt
Hi Allan,
I like to use the calculators on http://www.yourmortgage.com.au/calculators. You can’t download them but there are plenty to choose from for just about anything to do with a house. For example, you can’t work out stamp duty, how much it will cost to carpet your house, the overall costs involved in buying a house. Very handy!
Luckyone
Allan,I hope you got my e-mail.
No… For some reason I’m not receiving emails from people in the forum. I just noticed a 1 (one) looks the same as an l (L). So maybe that’s the problem…
(The L’s are actually number ones.)
So it’s: [email protected]
G’day Allan…
I have to agree with “Luckyone”… the http://www.yourmortgage.com.au/calculators website is one of my favourites. The one I love is the “Repayment Calculator – Advanced”. You can work out the regular minimum repayments. Then you put in extra payments and redraws for specific months, etc. So you can work out what effect it will have if you pay $500 extra per month, make a lump sum payment in month 7 and redraw $2000 in a year from now (for example).
Definitely worth having a look!! [
]Lozza
Here is the calculation for monthly repayments as from the westpac calculator from realestate.com.au:
=(B2*(1+B3/1200)^(B4*12)*B3/1200)/((1+B3/1200)^(B4*12)-1)
B2 is the pricipal amount (eg $100000)
B3 is the interest rate (eg 6.5)
B4 is the loan term (eg 30 years)To get the fortnightly repayments as from the westpac calculator use:
=(B2*(1+B3/2400)^(B4*24)*B3/2400)/((1+B3/2400)^(B4*24)-1)
I think this is wrong. 2400 should be 2600 and 24 should be 26. They use two fortnights to a month. They don`t count the extra fortnights. Similar for weeks. They use 48 weeks in a year, not 52.
Strange. Weekly is:=(B2*(1+B3/4800)^(B4*48)*B3/4800)/((1+B3/4800)^(B4*48)-1)
Put those into your spreadsheet and you will get the same as the westpac calculator.
Scott
]
]
The topic ‘Your favorite excel spreadsheets?’ is closed to new replies.
