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
- 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.
In the example below, we have a 30-year mortgage, $100k to be paid off monthly at an APR of 8%.
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.
