Excel for Finance Professionals

Unit 5: Financial Functions  

In this unit, you will learn how to:   

  Calculate with financial functions  

The most common use of Excel is to perform calculations involving money.  Every day people make thousands of financial decisions based on figures in a spreadsheet.  These decisions range from simple to complex.  Can I afford to buy a new car in the next 18 months?  Will a business result in a positive cash flow after 5 years?  

PMT  

The PMT (Payment Monthly Term) function returns the loan payment (principal plus interest) per period assuming constant payment amounts and a fixed interest rate.  

Syntax  

PMT(rate,nper,pv,[fv],[type])  

For example, suppose you borrow £6,000 to buy a car and plan to pay it off on monthly instalments at an interest rate of 6.25% over 18 months. The PMT function calculates that the repayment to be made is £350.07 at the end of every month.  

Note that Rate is the monthly interest rate so is divided by 12.  

Nper is the number of monthly payments to be made.  

Pv is the monthly payment amount and is entered as a negative because it is a payment.   

Fv is a cash balance you may wish to attain after the last payment is made (assumed to be 0 if omitted).   

Type determines when the monthly payment is to be paid (0 or omitted for end of month, 1 for beginning of month).  

Note that £350.07 x 18 = £6,301.24 so the amount of interest to be paid in this example is £301.24.   

FV   

The FV or Future Value function calculates the future value of an investment based on constant payments and a constant interest rate.  

Syntax  

FV(rate,nper,pmt,[pv],[type])  

For example, if you pay £250 into the bank every month at an annual interest rate of 6.5% for 2 years what will be the future value of your investment?  

In C6 create the following FV function by selecting Formulas, Financial, FV   

As before   

Rate is the monthly interest rate  

Nper is the number of months 24/12  

Pmt is -£250 as it is a payment  

PV is an optional lump-sum amount that is paid at the beginning of the investment.  In this case it is 0.  

Type is again for when the monthly payment is paid (End of the month if omitted). This function =FV(C5/12,24,-C4) returns £6,389.03 as the future value of the investment.  

Now suppose you invest £2,000 as a lump-sum as well as making regular payments at the end of each month for 2 years.   

Note again that the Pv and Pmt are entered as negative values because they are both payments.  

This time the FV function returns £8,665.89 which now includes the initial lump-sum with interest as well as the monthly payments and their interest.  

IRR  

Another useful financial function for businesses is the Internal Rate of Return IRR. This function calculates the rate of return on a series of regular cash flow income (positive values) and payments (negative values).   

Syntax  

IRR(values, [guess])  

In the example above the IRR is 9% for the income amounts shown and the initial cost payment of 70,000.  

Values:  the range of payment and income values.  

Guess:  is optional and set to 10% if omitted.   

Note: A #NUM! error message appears if an answer could not be found after 20 cycle calculations. In that case enter a guess for the IRR %.  

NPV  

NPV is a useful modelling function. It allows you to calculate the Net Present Value - the key word is present, i.e. the current value of your cashflows.  

Feed in your cashflows and rate to calculate the NPV.  

Syntax  

NPV(rate,value1, value2…)  

NPV assumes the cashflows are regular.  

A use of this could be comparing investing in a stock versus earning interest on the same amount in a bank account.  

XIRR  

XIRR behaves the same as IRR, but allows for irregular cashflows.  

Syntax  

XIRR(values, dates)  

In the example above, the cashflows are in column B and the dates are in column C. The formula in C9 will look like  

 =XIRR(B3:B7, C3:C7)  

 The internal rate of return is 37%.  

XNPV  

XNPV behaves the same as NPV, but allows for irregular cashflows.  

Syntax  

XNPV(rate,values,dates)  

In the above example a discount rate of 10% is applied over the cashflows for net present value of £2,954.46.  

Function with cashflows  

Assumptions to avoid  

APR vs APY  

Annual Percentage Rate (APR) is the annual rate of interest paid on credit products. It is generally used to allow borrowers to compare different offers. A mortgage with an interest rate of 4% could have an APR of 4.4% once other fees are taken into account.  

Annual Equivalent Rate (AER) is most often seen in relation to savings accounts. It demonstrates what rate of interest you will earn depending on how often interest is added to your account.  

An account that pays interest monthly will have a lower interest rate than one that pays annually because the account will benefit from compound interest sooner. They could have the same AER.