« Customizing a Toolbar | Main | Date & Time : Calculating Dates of Holidays »
October 01, 2004
Financial Functions 1 : Mortgage calculation
At some point, you will want to use OOo Calc to perform some sort of financial analysis. Like other topics I cover, you could write a whole book on this. As an introduction to the topic of financial functions in OOo Calc, we will look at a home mortgage example.
The PMT function calculates the regular payments needed to pay off a specified loan at a specified interest rate in a specified number of payments periods.
The arguments to the function are :
PMT(rate, nper, pv, fv, type) where
The monthly payment on this loan turns out to be $733.76. What we would also like to know is how our principal decreases over time. Each monthly payment is split between interest and principal. Early on in the repayment schedule, a greater percentage of the payment is set aside for interest.
The formula for calculating the principal balance at the end of each period (D10:D369) is as follows.
=FV(rate; nper; pmt; pv)
In this case, we are calculating the future value of loan one time period in the future, based on the present loan value and interest rate.
PMT(rate, nper, pv, fv, type) where
- rate The interest rate for each of the tune periods in the nper argument. (see below)
- nper The number of time periods between now and the end of the loan. For a standard 30-year ,ortgage - with monthy paymnets - this is 360.
- pv The present value (or initial value) of the loan. Also known as the principal
- type Optional argument which controls whether payments are made at the start of a period or the end.
The monthly payment on this loan turns out to be $733.76. What we would also like to know is how our principal decreases over time. Each monthly payment is split between interest and principal. Early on in the repayment schedule, a greater percentage of the payment is set aside for interest.
The formula for calculating the principal balance at the end of each period (D10:D369) is as follows.
=FV(rate; nper; pmt; pv)
In this case, we are calculating the future value of loan one time period in the future, based on the present loan value and interest rate.
Posted by Dave at October 1, 2004 09:34 PM
Comments
Thanks for that insightful comment! It makes for useful reading, especially when I need to make these types of calculations.
Posted by: Jessie at November 28, 2004 10:37 PM
Wonderful tutorial. It would be nice to have stuff like this bundled together as an optional download.
Posted by: Matt Wilson at December 10, 2005 08:12 AM