This is a mobile optimized page that loads fast, if you want to load the real page, click this text.

Formula to calculate loan repayments

Joined
14 December 2005
Posts
937
Reactions
1
Good evening to whoever is reading this

Earlier I went off topic in the Liberal v Labor thread when I started talking about calculating mortgage loan repayments.

Anyway, for anyone interested in calculating loan repayments and maximum loan amounts, attached is a spreadsheet that works it for you.

Be aware that results in the spreadsheet might be a little different to some online calculators which might use 26 fortnights and 52 weeks in a year and it certainly doesn't take into account any extra fees or criteria lending institutions might have.

My spreadsheet uses 26.089 fortnights (365.25/14) and 52.179 weeks (365.25/7) in a year. The 365.25 takes into account the extra day in leap years.

The formula behind the spreadsheet's number crunching is:


R = I x L / (1 - (1+I)^(-n))

Where:

R = Monthly/Fortnightly/Weekly Repayments

I = Interest Rate for the repayment period....ie....if annual interest rate = 7%
and repayments are monthly then I = 7/12

L = The Total Initial Loan Amount

n = the number of periods in the loan term....ie...if loan term = 20 years
and the repayments are monthly then n = 20 x 12

Anyway, for anyone interested feel free to download and play with it, hack/butcher it to suit. If anyone needs any help with it I'll try to reply tomorrow.

cheers

bullmarket
 

Attachments

  • repayments.xls
    16 KB · Views: 205
Re: Formula - Calculate Loan Repayments

I too went somewhat off topic in that thread with my argument about debt levels and housing, although it is a very mainstream political issue.

Anyway, here might be a good place to post this with regards to my previous comments about inflation eroding the value of debt.

The full article is here. http://www.bankofengland.co.uk/publications/speeches/2002/speech181.pdf

It's a speech from Mervyn King, then Deputy Governor of the Bank of England (UK equivalent of the RBA) on the very subject that inflation erodes the value of debt. It would seem that much the same circumstances also apply in Australia.

 
Re: Formula - Calculate Loan Repayments

For anyone interested, I've added another block to the original spreadsheet which allows you to calculate the length of repayment periods for a set of loan amount, interest rate and weekly/fortnightly/monthly repayment.

Anyone who knows their way around Excel could have added it themselves but for those who aren't too familiar with Excel the upgraded spreadsheet is attached below. The rest is the same as per my original post.

cheers

bullmarket
 

Attachments

  • repayments.xls
    17 KB · Views: 119
Cookies are required to use this site. You must accept them to continue using the site. Learn more...