Amortization Calculation in Excel worksheet

[Tiff]

I'm trying to figure out how to create an amortization
calculation in Excel using a mortgage amount, interest
rate, & monthly payment amount. I'm looking to
automatically calculate the number of payments, total
interest paid, & the last payment.

 

[Gord Dibben]

Tiff

How about a nice Mortgage Amortization Template?

http://office.microsoft.com/en-us/templates/TC010197771033.aspx?CategoryID=CT011377171033

Download and just type the data into the appropriate cells as instructed.


Gord Dibben Excel MVP

 

[Tiff]

Normally that would work, but it doesn't work for what I'm
looking for. The only information I have available at
that time I would be using the template is the loan
amount, the interest rate, the number of payments per
year, & the scheduled payment amount. The calculations in
the template will not work unless I enter the loan period
in years. There is no determined loan period for these
loans. I didn't know if there was a way that I can
manipulate the template to produce the information I'm
looking for. (Total # of payments, last payment amount, &
total interest.)

-----Original Message-----
Tiff

How about a nice Mortgage Amortization Template?

http://office.microsoft.com/en- us/templates/TC010197771033.aspx?CategoryID=CT011377171033

Download and just type the data into the appropriate cells as instructed.


Gord Dibben Excel MVP

 

[Gary Brown]

Tiff,
This formula will give you the number of periods in the loan.
=(LOG(Pmt)-LOG(-(Principal*InterestRate/12)+Pmt))/(LOG(1+(InterestRate/12)))
where...
Pmt is the scheduled payment amount
Principal is the loan amount
Interest Rate is the Annual interest rate

Hope this helps.
Gary Brown

Normally that would work, but it doesn't work for what I'm
looking for. The only information I have available at
that time I would be using the template is the loan
amount, the interest rate, the number of payments per
year, & the scheduled payment amount. The calculations in
the template will not work unless I enter the loan period
in years. There is no determined loan period for these
loans. I didn't know if there was a way that I can
manipulate the template to produce the information I'm
looking for. (Total # of payments, last payment amount, &
total interest.)

 

[Gary Brown]

Tiff,
This formula will give you the number of periods in the loan.
=(LOG(Pmt)-LOG(-(Principal*InterestRate/12)+Pmt))/(LOG(1+(InterestRate/12)))
where...
Pmt is the scheduled payment amount
Principal is the loan amount
Interest Rate is the ANNUAL interest rate

HTH,
Gary Brown

Normally that would work, but it doesn't work for what I'm
looking for. The only information I have available at
that time I would be using the template is the loan
amount, the interest rate, the number of payments per
year, & the scheduled payment amount. The calculations in
the template will not work unless I enter the loan period
in years. There is no determined loan period for these
loans. I didn't know if there was a way that I can
manipulate the template to produce the information I'm
looking for. (Total # of payments, last payment amount, &
total interest.)