# Formula to calculate loan repayments



## bullmarket (17 April 2006)

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


----------



## Smurf1976 (18 April 2006)

*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.



> The move from a regime of high inflation to one of price stability can have
> consequences which again are best illustrated by the housing market. A credible
> move to inflation targeting can bring down inflation expectations relatively quickly,
> even if with a lag. Chart 7 shows that inflation expectations, as measured by surveys,
> ...


----------



## bullmarket (18 April 2006)

*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


----------

