Share this content

Calculating Mortgage Repayments on Excel

Calculating Mortgage Repayments on Excel

Didn't find your answer?

Can any one tell me if it is possible to calculate payments for a repayment mortgage on Excel. And if so what the formula is or if there is a standard function.
Mahesh Vaswani

Replies (11)

Please login or register to join the discussion.

By Accounting WEB
24th Aug 2001 23:41

John watch the 9 O' clock watershed!

I am glad your question was posed after the 9 O'clock watershed. There are children visiting this site always for serious and indeed urgent tax and accounting problems!!

how is the dog?

I want bombard you with the football score!!

JAy Tanna

Thanks (0)
By othornto
26th Aug 2001 10:09

Template in Office
I havent looked recently but Office always did contain a Microsoft template for this that was quite useful, includes a graph and repay,ent table etc.

Thanks (0)
By Abacjm
24th Aug 2001 02:03

PMT on Excel
Is this the only example of PMT for Men as well as women?

Thanks (0)
By Accounting WEB
23rd Aug 2001 20:23

Excel does have PMT function

Exel does have the formula you are lloking for and it is:


Where rate is the rate, nper is number of periods, pv is the amount of mortgage/loan FV and type are optional.

Mortgage Amount: £100,000
Number of years: 20 = 240 months
Rate per year: 6.25 == 6.25%/12 [ per month ]
Mortgage Type: Repayment i.e. interest calculated daily

Using the formula you get:

=PMT(6.25%/12,240,100000) = £730.93

You can use cell references instead of hard wired numbers like I have done here.

You need to modify and adapt this to cater for different ways to charge interest. If you a specific example then tell us so that we can calculate for you.

Hope this helps.

Jay Tanna

Thanks (0)
Replying to ShirleyM:
By kudlit
01st Aug 2013 05:50

This really helped!

Thank you Jay. I was looking for this myself and I chanced upon the OP's question. 





Thanks (0)
By Anonymous
12th Aug 2009 18:56

repayment mortgage
would u be able to tell me how to solve this problem in excel??

From its pick at the end of 2007, house prices in London have dropped around 15% until now. Believing that this downward trend is about to be reversed, Tom Herbert, a single 26 year-old trying to get on “the property ladder” has identified a 1-bedroom flat he could buy for £200,000. Tom contacted a number of financial institutions and was offered the following mortgage options:
• repayment fixed rate for 2-years of 2.94%. After that period, the rate reverts to the bank’s standard variable rate, which currently is 5%;
• repayment fixed rate for 5-years of 3.45%. After that period, the rate reverts to the bank’s standard variable rate, which currently is 5%;
• interest only mortgage at 4% for the life of the loan. In this instance, you would be required to create an investment fund, which pays an interest rate of 2.5% to cover the repayment of the mortgage.
Assuming a 25-year mortgage period, please advise Tom on which option he should pursue, including an assessment of whether that advise would change if interest rates went up or down by up to three percentage points.

Thanks (0)
By Anonymous
12th Aug 2009 20:06

Talk about ...
bringing back the dead!

Thanks (0)
By Anonymous
12th Aug 2009 20:07

But how ...
did people manage to reply before the question had been asked?????

Thanks (0)
By GaryMc
13th Aug 2009 08:36

Wibbly wobbly timey wimey stuff
Doctor Who could answer that one

Thanks (0)
By xcs03
14th Apr 2010 12:07

Relating the Repayment mortgage

Was wondering if you were able to solve that problem?? relating to the repayment mortgage of Tom Herbert, 26 year old single...would really appreciate it cause you know the problem i am going through...

Thanks (0)
By TradeBrother
20th May 2012 10:51

Mortgage Calculation Workbook

This video will show you how to calculate a mortgage monthly repayment in Excel using the PMT function :-)

<iframe width="560" height="315" src="" frameborder="0" allowfullscreen></iframe>

Thanks (0)
Share this content

Related posts