is there any way to create a formula in excel for amortizing

[Guest]

i would like to create a spread sheet that will amortize a 30 year loan say
at 6% and be able to figure out a monthly payment without the calculator that
i have to do my job so others only have to enter certain values and come up
with their monthly payments

 

[Jim Cone]

My Excel add-in "Calculate Payments" may do what you want.
It adds a menu item to the Tools menu and displays an
input box to accept user queries.

Works for payment, term, loan amount or interest rate.
You provide any 3 of the above and it calculates the 4th.

Result displayed in a box, with one click transfer to the spreadsheet.
Comes with a one page Word.doc install/use file.
Available -free- upon direct request.
Remove XXX from my email address.

Jim Cone
San Francisco, USA
(e-mail address removed)



i would like to create a spread sheet that will amortize a 30 year loan say
at 6% and be able to figure out a monthly payment without the calculator that
i have to do my job so others only have to enter certain values and come up
with their monthly payments

 

[JE McGimpsey]

One way:

=PMT(rate, nper, pv)

So, for monthly payments on a $100,000 loan for 30 years at 6%:


A1: 100000
A2: 30
A3: 6%

A5: =PMT(A3/12, A2*12, -A1) ===> $599.55


Note that all values have to have the same units (so annual rate is
divided by 12 to get monthly rate, and years are multiplied by 12 to get
number of months)

Note also that PV and PMT are of opposite signs - think of it as inflow
and outflow.