Excel formula advice :)

tellmeitsfriday

Does anyone know a formula I can use in Excel to calculate how much I should pay per month to pay off a mortgage in a certain number of years.

I used to the mortgage calculator on here (so I can't rejig the formula myself) to find out that I will pay off in 16 years (instead of 24), but I would really like to make it 10 years.

Suggestions/Advice really appreciated

BenJMin

a very very crude way of doing it would be

In cell:

A1: Mortgage Outstanding
B1: <put in value of your mortgage outstanding>

A2: Years til mortgage free
B2: <put in number of years you want to be MF in>

A3: Months til mortgage free
B3: =sum(B2*12)

A4: Monthly payment needed
B4: =sum(B1/B3)

k3lvc

BenJMin wrote: »

a very very crude way of doing it would be

In cell:

A1: Mortgage Outstanding
B1: <put in value of your mortgage outstanding>

A2: Years til mortgage free
B2: <put in number of years you want to be MF in>

A3: Months til mortgage free
B3: =sum(B2*12)

D1: Monthly payment needed
D2: =sum(B1/B3)

Crude in that interest is missing from the equation :rotfl:

And no, it's school holidays and my brains switched off so I can't work out where an accurate interest rate calculation would slot in

BenJMin

k3lvc wrote: »

Crude in that interest is missing from the equation :rotfl:

Did say it was very crude :P

Mallotum_X

=PMT((((B2+1)^(1/12))-1),B4,B3)

B2 = rate eg 5%
B3 = Amount outstanding
B4 = Number of months left

scottishlass

Mallotum has beaten me to it but I did it this way:

A1 = Mortgage amount
A2 = Inetrest
A3 = Years to pay

A5 =A2/(12*100)
A6 =A3*12
A7 =A1*(A5/(1-(1+A5)^-A6))

You can then change A3 to see payments etc

tellmeitsfriday

Thank you people

Although not as automated as I had originally intended, it's such a simple solution that I like it Sometimes, no matter how hard I try, I can't see it.

Thank you again!

Halin_Brown

Thank You, how hard i try