Amortization Calculation in Excel worksheet
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. |
Tiff
How about a nice Mortgage Amortization Template? http://office.microsoft.com/en-us/te...CT011377171033 Download and just type the data into the appropriate cells as instructed. Gord Dibben Excel MVP On Thu, 27 Jan 2005 12:01:00 -0800, "Tiff" wrote: 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. |
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 On Thu, 27 Jan 2005 12:01:00 -0800, "Tiff" wrote: 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. . |
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 "Tiff" wrote in message ... 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 On Thu, 27 Jan 2005 12:01:00 -0800, "Tiff" wrote: 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. . |
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 "Tiff" wrote in message ... 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 On Thu, 27 Jan 2005 12:01:00 -0800, "Tiff" wrote: 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. . |
All times are GMT +1. The time now is 12:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com