Australian (ASX) Stock Market Forum

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.

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,
fell steadily following the introduction of inflation targeting, and are now anchored on
the 2.5% target. But a move to low inflation has other consequences that may be less
easily understood.

Price stability means lower nominal interest rates, and lower
mortgage interest payments. It may also mean lower real interest rates if the inflation
risk premium falls. But the fall in nominal rates is likely to be much larger than the
fall in real rates. The lower mortgage payment largely reflects a rise in the effective
duration of the loan because inflation no longer erodes the real value of the debt as
quickly as before. In a low inflation world, nominal incomes rise more slowly than
before and the real burden of servicing the debt persists. It may take longer for
households to work out the impact of low inflation on real interest rates than to realise
that the rate of increase of prices of everyday purchases has fallen. Learning takes
time.

One possible consequence of a slow adjustment to low inflation is that households
may mistake too much of the reduction in nominal interest rates for a permanent fall
in the real rate. As a result, asset prices are bid up to levels that prove unsustainable
when learning finally occurs – and at the LSE you know that in time we do learn.
 
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
Top