How to calculate a mortgage payment?

[Guest]

Hi,

I need to work out what a potential monthly mortgage payment would be in
excel but the calculation is too complicated for me.

Is there an easy way to create this calculation. Maybe a function that does
it for you?

Any help is greatly appreciated.

Tx

Suzanne

 

[JE McGimpsey]

Take a look at the PMT function in Help.

 

[Noob Jedi]

Hi,

I need to work out what a potential monthly mortgage payment would be in
excel but the calculation is too complicated for me.

Is there an easy way to create this calculation. Maybe a function that does
it for you?

Any help is greatly appreciated.

Tx

Suzanne

That's funny because I JUST made this calculator in Excel a couple
weeks ago. Also, I made this for considering a car, so I'm not sure
how much it applies to a house, but I'm thinking it's the same thing.
Anyways, I'm sure the experts here will probably know a better way
then me, but this is what I did:

C2 = Loan Amount
C3 = Interest Rate %
C4 = C3/C7
C5 = Number of periods (Average mortgage is 30 years, so imagine 30
years times 12 months is 360 months)
C6 = Number of Years
C7 = Will display you total years

B13 = Remaining periods (e.g. 360)
.....
to the last month

C13 = C2*$C$4/(1-((1+$C$4)^-B13))
.....
Carrry it down to however many periods you anticipate you will pay.


It would be wise to also consider the principle and interest
Principle, I put:
D13 = C13-E13

Interest:
E13 = $C$4*F12

I hope this helps. I made it rather sloppy, but it was just to get the
concept down.

 

[Noob Jedi]

Take a look at the PMT function in Help.






- Show quoted text -

Wow, told you I suck. That was way more simple than mine haha. Other
then mine showing the complete schedule, this one is better than mine.
Haha.

 

[Guest]

Actually I can use both the PMT and your solution so thanks very much

nice work

S

 

[Guest]

Thanks JE I didn't know about that function

Take a look at the PMT function in Help.