Formula to calculate loan repayments - Aussie Stock Forums

Results 1 to 3 of 3
  1. #1
    bullmarket's Avatar
    Join Date
    Dec 2005
    Looking over your right shoulder :)

    Default Formula to calculate loan repayments

    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))


    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.


    Attached Files

  2. #2

    Default 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/publi.../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

    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.

  3. #3
    bullmarket's Avatar
    Join Date
    Dec 2005
    Looking over your right shoulder :)

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


    Attached Files

Similar Threads

  1. How to calculate return
    By Sicilian Trader in forum Beginner's Lounge
    Replies: 35
    Last Post: 10th-April-2009, 04:45 PM
  2. How to calculate look through gearing?
    By jauggy in forum Stock Market Nuts and Bolts
    Replies: 0
    Last Post: 23rd-January-2009, 07:22 AM
  3. How to calculate probabilty using options
    By Seneca60BC in forum Derivatives
    Replies: 6
    Last Post: 23rd-October-2008, 09:29 AM
  4. ANZ Mortgage repayments
    By kangaroo19 in forum General Investment and Economics
    Replies: 2
    Last Post: 11th-September-2008, 12:42 AM
  5. Marginal loan or Investment loan?
    By kerosam in forum Stock Market Nuts and Bolts
    Replies: 19
    Last Post: 13th-July-2006, 10:56 PM


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Aussie Stock Forums