Financial Functions in Excel

(A very nice sample sheet for Solver can be found here: SOLVSAMP.XLS -- right-click and select "Save target as..."
Be sure that you have the Solver Add-in installed: If not, go to "Tools/Add-ins" and select "Solver Add-in")

Here are some commonly used financial functions in Excel:

There are 4 quantities that are interelated:

1. Present Value (PV) = the value of the load or savings today.
2. Interest Rate per period (RATE) = the interest rate, usually monthly (if given an annual rate, be sure to divide by 12!)
3. Number of payment periods (NPER) = Number of payment periods (number of months usually).
4. Periodic payment (PMT) = The payment per period (monthly payment usually). This value is the same for every payment period.

Given any 3 of the above 4 quantities, you can calculate the 4'th.

Use the following functions to calculate the missing value:

• PMT

• Used to calculate the fixed periodic payment, given the present value, the periodic interest rate and the total number of payments.
• Note that if you are given an annual interest rate, you must divide it by the number of payment periods per year (12 for monthly payments).
• Some sub-calculations involving the monthly payment that are used in creating amortization tables are:
  • IPMT
    • Calculates the interest portion of a PMT for a given month. The NPer input is the number of elapsed payment periods, not the total number of payment periods.
  • PPMT
    • Calculates the principal portion of a PMT for a given month. The NPer input is the number of elapsed payment periods, not the total number of payment periods.
      
      

• NPER

  • Used to calculate the total number of payment periods needed to pay off a given present value with a given periodic payment and a given periodic interest rate.
  • Note that if you are given an annual interest rate, you must divide it by the number of payment periods per year (12 for monthly payments).
    
    

• RATE

  • Used to calculate the periodic interest rate required to pay off a given present value with a given periodic payment and a given total number of payments. Note that if you want the annual interest rate, you must multiply this result by the number of payment periods per year (12 for monthly payments).
    
    

• PV

  • Used to calculate the size loan that could be paid of given a given periodic payment over a given total number of payments and a given periodic interest rate.
  • Note that if you are given an annual interest rate, you must divide it by the number of payment periods per year (12 for monthly payments).

Another function that is commonly used in financial worksheets:

• FV

  • The future value is the value of a loan or investment after a series of periodic payments.
  • The FV function is used to calculate the future value, given a periodic interest rate, the number of payment periods elapsed at the time of when future value is to be determined (i.e. it is not necessarily the total number of payments!), the payment amount and the present value.
  • The FV function can be used both to see the running balance in a loan amortization table (may produce the negative of the desired number however) or to see the result of a periodic investement (savings).

An example worksheet that uses all these functions can be downloaded here: finance.xls -- right-click and select "Save target as..."

Glossary

Amoritize: To amortize a loan is to calculate a series of constant periodic payments to pay off both the principal (present value) of a loan as well as all accrued interest, based on a given periodic interest rate and a given total number of payments.

Amortization Table: An amortization table is a table that shows the interest being paid, the principle being paid and the current balance for each payment period of a loan. If the interest rate is constant, the interest and principle payments should sum to a constant value, which can be directly calculated using the PMT function.