Mortgage repayments, Excel spreadsheet

denisiw

Early in March I downloaded an Excel spreadsheet from MSE. A couple of weeks ago my hard drive packed up and I lost it. I've been searching on here for two hours and can't find it! I've searched on mortgage amortization as that was the term that was used, no luck.

Would someone point it out for me please?

Thank you.

andy777_2

Hi,

I'm new on here so I can't help with the location of the spreadsheet. However, if you're stuck for a solution, use the PMT function to work out the monthly payments and go from there. If you look in the Excel help (see Financial Functions) you'll find a host of functions to help you to build your own amortisation table.

Otherwise go to the fount of all knowledge - Google - and find one there.

Cheers,
A

alanobrien

denisiw wrote:

Early in March I downloaded an Excel spreadsheet from MSE. A couple of weeks ago my hard drive packed up and I lost it. I've been searching on here for two hours and can't find it! I've searched on mortgage amortization as that was the term that was used, no luck.

Would someone point it out for me please?

Thank you.

Was it the budget planner here ?

http://www.moneysavingexpert.com/cgi-bin/viewnews.cgi?newsid1089226742,17582,

xlt_hunter

The thread is here for the excel file:
http://forums.moneysavingexpert.com/showthread.html?t=158046

HTH

Michael

tomstickland

If you want to work out the monthly payment on a repayment mortgage use:

if a is annual rate in %

Monthly rate = 1 + a / 1200

For a loan of L

and n payments (300 for a 25 year term)

m = L * r^n * (r - 1 ) / (r^n -1)

Alternatively, if you want to work out how long it will take to pay off a loan of L when you can afford m per month:

n = log (m / ( m - L*(r-1))) / log (r)

Making a direct spreadsheet is very easy.
Take total amount owed at end of month, multiply by r and then subract monthly payment. Repeat for each month.

denisiw

xlt_hunter wrote:

The thread is here for the excel file:
http://forums.moneysavingexpert.com/showthread.html?t=158046

HTH

Michael

That's the one I was looking for.

Much appreciated!

Denis

denisiw

I tried to change the number of years and got an error message saying "Please enter a whole number of years from 1 to 30."

I didn't have this problem the last time I downloaded and used it.

Can anyone shed any light on this please?

Thanks.

JollyNolly

denisiw wrote:

I tried to change the number of years and got an error message saying "Please enter a whole number of years from 1 to 30."

I didn't have this problem the last time I downloaded and used it.

Can anyone shed any light on this please?

Thanks.

It will do this if you try to put in fractions of a year - e.g. 15.5 for 15 years and 6 months. It's because they've put in some data validation for some reason. You can try taking it off by doing this:

• Click on cell D8
• Click on Data, then Validation
• In the "allow" box change it from "whole number" to "any value"
• click on OK and see if it does what you want

denisiw

JollyNolly wrote:

It will do this if you try to put in fractions of a year - e.g. 15.5 for 15 years and 6 months. It's because they've put in some data validation for some reason. You can try taking it off by doing this:

• Click on cell D8
• Click on Data, then Validation
• In the "allow" box change it from "whole number" to "any value"
• click on OK and see if it does what you want

I was putting in a whole number of years, but thanks to your reply I've found an easier way round it. You just have to add a decimal point eg 15. not 15 and it accepts it.

Thanks for the inspiration.

Denis