Instructor-led training - excel for finance professionals training course london and uk wide

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

# Excel for Finance Professionals

Face to face / Virtual closed & onsite training. Restaurant lunch included at STL venues.

• 1 day Instructor-led workshop

Related courses: Finance for nonfinancial London.

## Training manual sample

Below are some extracts from our Excel for Finance Professionals manual.

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 moneyEvery day people make thousands of financial decisions based on figures in a spreadsheetThese decisions range from simple to complexCan I afford to buy a new car in the next 18 monthsWill 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 investmentIn 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.

Thanks. Your download will begin shortly.

## Please help us

Share or create a link to this manual today!

Just follow these simple instructions...

Server loaded in 0.23 secs.