EXCEL FOR FINANCE STUDENTS
The Complete Guide to Financial Functions in Excel
What's Inside:
✓ 30+ Essential Excel Functions for Finance
✓ Organised by Finance Topic (TVM, NPV, IRR, CAPM, Bonds, WACC)
✓ Step-by-Step Screenshots
✓ Real Financial Examples
✓ Common Errors & How to Fix Them
✓ Practice Exercises with Solutions
Works with Excel 2016, 2019, 2021 & Microsoft 365
,Table of Contents
Section 1: Time Value of Money (TVM) .......................... Page 3
FV, PV, PMT, NPER, RATE
Section 2: Investment Appraisal .......................... Page 10
NPV, XNPV, IRR, XIRR, MIRR
Section 3: Bond Valuation .......................... Page 17
PV for bonds, PRICE, YIELD, DURATION
Section 4: Loan Amortisation .......................... Page 22
PMT, IPMT, PPMT, CUMIPMT, CUMPRINC
Section 5: CAPM & Portfolio Analysis .......................... Page 28
SLOPE, STDEV, AVERAGE, COVARIANCE, CORREL
Section 6: WACC Calculation .......................... Page 33
SUMPRODUCT, weighted averages
Section 7: Depreciation .......................... Page 36
SLN, DB, DDB, VDB
Section 8: Essential Data Functions .......................... Page 39
VLOOKUP, INDEX-MATCH, IF, SUMIF, COUNTIF
Section 9: Formatting & Shortcuts .......................... Page 44
Number formats, keyboard shortcuts, tips
, Section 1: Time Value of Money (TVM)
Time Value of Money is the foundation of all finance. These Excel functions help you calculate how money grows over time and what future cash
flows are worth today.
When You'll Use This:
Calculating how much your savings will grow
Finding the present value of future payments
Determining loan payments
Planning for retirement or future expenses
1.1 FV Function (Future Value)
Calculates what an investment will be worth in the future, given regular payments and compound interest.
=FV(rate, nper, pmt, [pv], [type])
Argument Description Example
rate Interest rate PER PERIOD 0.05 (5%) or 0.05/12 for monthly
nper Total number of periods 10 years or 10*12 for months
pmt Payment made each period (usually negative) -1000
[pv] Present value / initial investment (optional) -5000
[type] 0 = end of period, 1 = beginning 0 (default)
Example: Savings Growth
You invest £5,000 today and add £200 per month for 10 years at 6% annual interest (0.5% monthly). How much will you have?
=FV(0.06/12, 10*12, -200, -5000, 0)
Result: £41,857.14
TIP: Payments (pmt) and present value (pv) are entered as NEGATIVE because they represent cash OUT of your pocket. The result is
positive because it's cash you'll receive.
⚠ COMMON ERROR: Forgetting to match the rate and nper periods!
If payments are monthly, divide annual rate by 12 AND multiply years by 12.
1.2 PV Function (Present Value)
Calculates what a future sum of money or stream of payments is worth TODAY.
=PV(rate, nper, pmt, [fv], [type])
Argument Description Example
rate Discount rate per period 0.08 (8%)
nper Total number of periods 5
pmt Payment received each period 1000
Future value at end (optional)
[fv] 10000
[type] 0 = end, 1 = beginning 0
The Complete Guide to Financial Functions in Excel
What's Inside:
✓ 30+ Essential Excel Functions for Finance
✓ Organised by Finance Topic (TVM, NPV, IRR, CAPM, Bonds, WACC)
✓ Step-by-Step Screenshots
✓ Real Financial Examples
✓ Common Errors & How to Fix Them
✓ Practice Exercises with Solutions
Works with Excel 2016, 2019, 2021 & Microsoft 365
,Table of Contents
Section 1: Time Value of Money (TVM) .......................... Page 3
FV, PV, PMT, NPER, RATE
Section 2: Investment Appraisal .......................... Page 10
NPV, XNPV, IRR, XIRR, MIRR
Section 3: Bond Valuation .......................... Page 17
PV for bonds, PRICE, YIELD, DURATION
Section 4: Loan Amortisation .......................... Page 22
PMT, IPMT, PPMT, CUMIPMT, CUMPRINC
Section 5: CAPM & Portfolio Analysis .......................... Page 28
SLOPE, STDEV, AVERAGE, COVARIANCE, CORREL
Section 6: WACC Calculation .......................... Page 33
SUMPRODUCT, weighted averages
Section 7: Depreciation .......................... Page 36
SLN, DB, DDB, VDB
Section 8: Essential Data Functions .......................... Page 39
VLOOKUP, INDEX-MATCH, IF, SUMIF, COUNTIF
Section 9: Formatting & Shortcuts .......................... Page 44
Number formats, keyboard shortcuts, tips
, Section 1: Time Value of Money (TVM)
Time Value of Money is the foundation of all finance. These Excel functions help you calculate how money grows over time and what future cash
flows are worth today.
When You'll Use This:
Calculating how much your savings will grow
Finding the present value of future payments
Determining loan payments
Planning for retirement or future expenses
1.1 FV Function (Future Value)
Calculates what an investment will be worth in the future, given regular payments and compound interest.
=FV(rate, nper, pmt, [pv], [type])
Argument Description Example
rate Interest rate PER PERIOD 0.05 (5%) or 0.05/12 for monthly
nper Total number of periods 10 years or 10*12 for months
pmt Payment made each period (usually negative) -1000
[pv] Present value / initial investment (optional) -5000
[type] 0 = end of period, 1 = beginning 0 (default)
Example: Savings Growth
You invest £5,000 today and add £200 per month for 10 years at 6% annual interest (0.5% monthly). How much will you have?
=FV(0.06/12, 10*12, -200, -5000, 0)
Result: £41,857.14
TIP: Payments (pmt) and present value (pv) are entered as NEGATIVE because they represent cash OUT of your pocket. The result is
positive because it's cash you'll receive.
⚠ COMMON ERROR: Forgetting to match the rate and nper periods!
If payments are monthly, divide annual rate by 12 AND multiply years by 12.
1.2 PV Function (Present Value)
Calculates what a future sum of money or stream of payments is worth TODAY.
=PV(rate, nper, pmt, [fv], [type])
Argument Description Example
rate Discount rate per period 0.08 (8%)
nper Total number of periods 5
pmt Payment received each period 1000
Future value at end (optional)
[fv] 10000
[type] 0 = end, 1 = beginning 0
