EXCEL FOR FINANCE - QUICK REFERENCE CHEAT SHEET
TIME VALUE OF MONEY BONDS
Future Value (FV) Bond Price (using PV)
=FV(rate, nper, pmt, [pv], [type]) =PV(YTM, years, -coupon, -face_value)
Example: =FV(0.06/12, 10*12, -200, -5000) =PV(0.08, 10, -60, -1000)
Calculates future value of investment with regular payments If Then
YTM > Coupon Discount (Price < Face)
YTM < Coupon Premium (Price > Face)
Present Value (PV)
YTM = Coupon Par (Price = Face)
=PV(rate, nper, pmt, [fv], [type])
Example: =PV(0.08, 5, 1000)
Yield to Maturity (using RATE)
What future cash flows are worth today
=RATE(years, coupon, -price, face_value)
Payment (PMT) CAPM & PORTFOLIO
=PMT(rate, nper, pv, [fv], [type])
Beta (using SLOPE)
Example: =PMT(0.045/12, 25*12, 250000)
=SLOPE(stock_returns, market_returns)
Monthly payment for loan/mortgage
Beta Meaning
β>1 More volatile (aggressive)
Number of Periods (NPER) β=1 Same as market
=NPER(rate, pmt, pv, [fv], [type]) β<1 Less volatile (defensive)
How long to pay off loan or reach goal
CAPM Expected Return
E(R) = Rf + β × (Rm - Rf)
Interest Rate (RATE)
=RATE(nper, pmt, pv, [fv], [type]) =B1 + B2 * (B3 - B1)
Find interest rate (returns rate per period)
Portfolio Beta
INVESTMENT APPRAISAL =SUMPRODUCT(weights, betas)
Net Present Value (NPV)
Risk & Correlation
=NPV(rate, values) + Initial_Investment
STDEV.S(range) Standard deviation
⚠ ADD initial investment separately! AVERAGE(range) Mean return
=NPV(0.10, B2:B5) + B1 CORREL(range1,range2) Correlation
COVARIANCE.S(r1,r2) Covariance
Decision: NPV > 0 → Accept ✓
WACC
NPV with Dates (XNPV)
=XNPV(rate, values, dates) WACC = (E/V)×Re + (D/V)×Rd×(1-Tc)
Use when cash flows are at irregular intervals Using SUMPRODUCT:
=SUMPRODUCT(weights, costs)
Remember: Debt cost is AFTER-TAX!
Internal Rate of Return (IRR)
=IRR(values, [guess])
DEPRECIATION
=IRR(A1:A5) — includes initial investment
Decision: IRR > Required Return → Accept ✓ Function Method
SLN(cost,salvage,life) Straight-line
DB(cost,salvage,life,period) Declining balance
IRR with Dates (XIRR)
DDB(cost,salvage,life,period) Double declining
=XIRR(values, dates, [guess])
ESSENTIAL DATA FUNCTIONS
Modified IRR (MIRR)
Function Use For
=MIRR(values, finance_rate, reinvest_rate)
VLOOKUP(val,table,col,FALSE) Look up data
More realistic than IRR
INDEX(range,MATCH(val,col,0)) Flexible lookup
SUMIF(range,criteria,sum_range) Sum with condition
LOAN AMORTISATION COUNTIF(range,criteria) Count with condition
IFERROR(formula,value) Handle errors
Function Purpose
IPMT(rate,per,nper,pv) Interest portion of payment
PPMT(rate,per,nper,pv) Principal portion of payment
CUMIPMT(rate,nper,pv,start,end,type) Cumulative interest
CUMPRINC(rate,nper,pv,start,end,type) Cumulative principal
IPMT + PPMT = PMT (always!)
TIME VALUE OF MONEY BONDS
Future Value (FV) Bond Price (using PV)
=FV(rate, nper, pmt, [pv], [type]) =PV(YTM, years, -coupon, -face_value)
Example: =FV(0.06/12, 10*12, -200, -5000) =PV(0.08, 10, -60, -1000)
Calculates future value of investment with regular payments If Then
YTM > Coupon Discount (Price < Face)
YTM < Coupon Premium (Price > Face)
Present Value (PV)
YTM = Coupon Par (Price = Face)
=PV(rate, nper, pmt, [fv], [type])
Example: =PV(0.08, 5, 1000)
Yield to Maturity (using RATE)
What future cash flows are worth today
=RATE(years, coupon, -price, face_value)
Payment (PMT) CAPM & PORTFOLIO
=PMT(rate, nper, pv, [fv], [type])
Beta (using SLOPE)
Example: =PMT(0.045/12, 25*12, 250000)
=SLOPE(stock_returns, market_returns)
Monthly payment for loan/mortgage
Beta Meaning
β>1 More volatile (aggressive)
Number of Periods (NPER) β=1 Same as market
=NPER(rate, pmt, pv, [fv], [type]) β<1 Less volatile (defensive)
How long to pay off loan or reach goal
CAPM Expected Return
E(R) = Rf + β × (Rm - Rf)
Interest Rate (RATE)
=RATE(nper, pmt, pv, [fv], [type]) =B1 + B2 * (B3 - B1)
Find interest rate (returns rate per period)
Portfolio Beta
INVESTMENT APPRAISAL =SUMPRODUCT(weights, betas)
Net Present Value (NPV)
Risk & Correlation
=NPV(rate, values) + Initial_Investment
STDEV.S(range) Standard deviation
⚠ ADD initial investment separately! AVERAGE(range) Mean return
=NPV(0.10, B2:B5) + B1 CORREL(range1,range2) Correlation
COVARIANCE.S(r1,r2) Covariance
Decision: NPV > 0 → Accept ✓
WACC
NPV with Dates (XNPV)
=XNPV(rate, values, dates) WACC = (E/V)×Re + (D/V)×Rd×(1-Tc)
Use when cash flows are at irregular intervals Using SUMPRODUCT:
=SUMPRODUCT(weights, costs)
Remember: Debt cost is AFTER-TAX!
Internal Rate of Return (IRR)
=IRR(values, [guess])
DEPRECIATION
=IRR(A1:A5) — includes initial investment
Decision: IRR > Required Return → Accept ✓ Function Method
SLN(cost,salvage,life) Straight-line
DB(cost,salvage,life,period) Declining balance
IRR with Dates (XIRR)
DDB(cost,salvage,life,period) Double declining
=XIRR(values, dates, [guess])
ESSENTIAL DATA FUNCTIONS
Modified IRR (MIRR)
Function Use For
=MIRR(values, finance_rate, reinvest_rate)
VLOOKUP(val,table,col,FALSE) Look up data
More realistic than IRR
INDEX(range,MATCH(val,col,0)) Flexible lookup
SUMIF(range,criteria,sum_range) Sum with condition
LOAN AMORTISATION COUNTIF(range,criteria) Count with condition
IFERROR(formula,value) Handle errors
Function Purpose
IPMT(rate,per,nper,pv) Interest portion of payment
PPMT(rate,per,nper,pv) Principal portion of payment
CUMIPMT(rate,nper,pv,start,end,type) Cumulative interest
CUMPRINC(rate,nper,pv,start,end,type) Cumulative principal
IPMT + PPMT = PMT (always!)
