Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. . |
#4
|
|||
|
|||
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. . |
#5
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
How do I convert an existing MS Excel worksheet tracking a simple. | Excel Discussion (Misc queries) | |||
Need to remove a password that noone placed on Excel worksheet. | Excel Worksheet Functions |