mortgage-style calculation

[Guest]

Is there a way for Excel to structure a mortgage style calculation so that
yearly payments are equal but where principal and interest are not paid at
the same time? For example, interest would pay monthly but pricipal would be
paid on a yearly basis. (actual payment dates would be the same, meaning
that interest would always pay on the first of the month and at the end of
the year, principal would also pay on the first of the month). In other
words:

Jan1 Interest
Feb1 Interest
....
Nov1 Interest
Dec1 Principal and interest

The PMT function is set up so that principal and interest are always paid
together.

 

[Guest]

PPHi,

Why not set up two formulas, one using IPMT, the other PPMT.

 

[Guest]

that's what I ended up doing. And just dividing the IPMT by 12 to have
monthly payments with no amortization. It seems to work after looking at it
a few ways. Thanks

 

[Oscar]

Hi All,

I am completely new to ExCel so what you have explained are all alien
language to me. However, I think they are to do with the Function menu, right?

Anyway I too wanted to find out my mortgage payment on a monthly basis on a
fix rate for a duration of term. Can someone show me a step-by-step of how to
do this? Much appreciated.

Thanks
Oscar

 

[Fred Smith]

Oscar,

Don't hijack threads. If you something new to ask, start a new thread.

You want the PMT function. Something like:
=PMT(5%/12,30*12,100000)

Regards,
Fred.