Excel for finance students
A lot of a corporate finance unit is the time value of money, and the assignments are often done in Excel, even when the exam is still you and a financial calculator.
I've put the lessons in roughly the order a unit gets to them, so you can do each one the week it comes up in lectures. Compounding comes first, then annuities and loans, then valuing projects and bonds. Each one is a short task on a spreadsheet that checks your formula as you go, and takes about ten minutes.
Session 1: Set up
Finance sheets have a column for every period, so you'll spend a lot of time copying one formula across. These two are free and make that a lot less painful.
Session 2: Compounding
Pretty much everything else in the unit builds on future and present value. These lessons do them by hand first and then with the functions, so you can see the function is doing the same maths.
- LESSON 5 · PASSOperators and order of operationsWhere the brackets go in (1+r)^n.PASS
- LESSON 31 · PASSCompounding and FV: what cash set aside today grows toWhat an amount grows to, first with (1+r)^n and then with FV.PASS
- LESSON 32 · PASSPresent value and discount factors: what an earn-out is worth todayDiscount factors, and what a set of future payments is worth today.PASS
- LESSON 36 · PASSEFFECT and NOMINAL: comparing rates that compound differentlyComparing rates that compound monthly, quarterly and yearly.PASS
Session 3: Annuities
PMT, RATE and NPER all work from the same inputs, and the bit that catches people out is the signs (why is my repayment negative?). Do the signs lesson first and the other two get a lot easier.
- LESSON 33 · PASSSign conventions: why PMT, PV and FV come out negativeWhy PMT, PV and FV come out negative, and what to do about it.PASS
- LESSON 34 · PASSPMT: repayments on equipment finance, in arrears, in advance and with a balloonRepayments in arrears and in advance, and with a balloon at the end.PASS
- LESSON 42 · PASSRATE and NPER: the rate inside a lease, how long it takes to repay, and checking it with Goal SeekThe interest rate inside a lease, and how long a loan takes to repay.PASS
Session 4: Loan schedules
If your unit has an Excel assignment, there's a good chance it includes a full amortisation table, with each repayment split into interest and principal.
- LESSON 35 · PASSIPMT and PPMT: splitting a loan repayment into interest and principalHow much of one repayment is interest and how much pays down the loan.PASS
- LESSON 41 · PASSLoan amortisation schedule: opening balance, interest, repayment and closing balanceOpening balance, interest, repayment and closing balance for every period.PASS
Session 5: Valuing cash flows
Later in the unit you value projects and bonds, which pulls together everything above.
- LESSON 37 · PASSNPV: valuing a project, and the year 0 trap in Excel's NPVNPV for a project, and where the year 0 cash flow goes.PASS
- LESSON 38 · PASSIRR and XIRR: the return on an investment, and why real cash flows need datesThe return on an investment, and why real dates change it.PASS
- LESSON 39 · PASSPerpetuities: level and growing, and how they become a terminal valueLevel and growing perpetuities, and turning one into a terminal value.PASS
- LESSON 40 · PASSBond pricing: semi-annual coupons by hand, with PV, and with PRICE and YIELDPricing a bond with semi-annual coupons, by hand and with PRICE and YIELD.PASS
Session 6: Cost of capital and valuing a company
Many units finish with the cost of capital and a company valuation, which uses NPV and perpetuities to value a whole business.
It's worth reading the set-up tips too (a few settings that make the shortcuts work properly), and keeping the shortcut list open while you practise.
Questions
Can I use Excel in my finance exam?
Usually not. Most finance exams are still on paper with a financial calculator, so you need to know the formulas either way. Excel is for assignments, checking your working and eventually the job. Several lessons here do the maths by hand first and then with the function, so you practise both.
Why does PMT give me a negative number?
It's Excel's sign convention. Money you receive is positive and money you pay out is negative, so if the loan goes in as a positive PV, the repayment comes back negative. Put a minus sign in front of PMT, or enter the loan as a negative. The sign conventions lesson goes through it.
Why doesn't Excel's NPV match my textbook?
Excel's NPV treats the first cash flow in the range as one period away, so it discounts the year 0 amount too. Leave year 0 out of the range and add it on separately, like =NPV(rate, year 1 to year 5) + year 0.
Do these work in Google Sheets?
Yes. Google Sheets has the same finance functions and every lesson here has a Sheets version, except the Goal Seek step at the end of the RATE and NPER lesson. Switch to Sheets in the settings at the top of the page.
Is this the same as financial modelling?
No. These lessons cover the finance maths a uni unit teaches. A financial model links a whole set of forecasts together. These lessons are pieces a model uses, but they don't build one.
Semester pass
Lesson 1 is free to try and lessons 2 to 4 need a free account. The pass unlocks every lesson on this page and the rest, in Excel and Google Sheets. A$49 every six months; cancel any time. Terms and refunds.