# Variables required to create an amortization schedule in Excel

Hello,

For a loan involving monthly payments, what combinations of the following variables are needed to create an amortization schedule?

Beginning principal amount
Interest rate (fixed APR)
# of months
Monthly payment

For example, if you have the first three variables, I'm pretty sure you can calculate the fourth. Is that correct?

Beginning principal amount:  \$87,500.00
Interest rate (fixed APR):  5.99%
# of months: 264
Monthly payment:  ??

Alternatively, suppose you've got a different three variables?

Beginning principal amount:  \$87,500.00
Interest rate (fixed APR):  5.99%
# of months: ??
Monthly payment:  \$582

Can you solve for the fourth?

What formulas are involved in these calculations?

This question is more about solving for the missing variable. Therefore, I will ask about creating an amortization schedule in a separate thread.

Thanks
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Microsoft Excel ExpertCommented:
=PMT(5.99%/264, 264, 87500, 0, 1)
=NPER(5.99%/264, -582, 87500)

For the first question use PMT

For the second use NPER
Author Commented:
Thanks for the responses.

aburr

ProfessorJimJam,
Thanks for the two Excel functions.

I also found two more functions which actually enable the creation of a schedule:

=CUMPRINC()
=CUMIPMT()

Experts Exchange Solution brought to you by