📊

Excel Financial Formulas Lecture

Jul 14, 2024

Excel Financial Formulas Lecture

Overview

  • Three Levels of Financial Formulas:
    1. Basic Functions: Time Value of Money
    2. Intermediate Functions: Capital Budgeting (NPV, IRR)
    3. Advanced Functions: Loan Schedule

Basic Functions

Present Value (PV)

  • Scenario: Buying a $100,000 car in 3 years
  • Inputs:
    • Future Value (FV): $100,000
    • Number of periods: 3 years
    • Interest rate: 8%
  • Formula: =PV(rate, nper, pmt, [fv], [type])
    • =PV(0.08, 3, , 100000)
    • Result: $79,000 (Amount needed today)

Future Value (FV)

  • Scenario: Invest $79,000 for 30 years
  • Inputs:
    • Present Value (PV): $79,000
    • Number of periods: 30 years
    • Interest rate: 8%
  • Formula: =FV(rate, nper, pmt, [pv], [type])
    • =FV(0.08, 30, , -79000)
    • Result: ~$800,000 (Value in 30 years)

Future Value with Recurring Payments

  • Scenario: Investing $79,000 initially + $2000 annually for 30 years
  • Inputs:
    • Interest rate: 8%
    • Number of periods: 30 years
    • Recurring payment: $2000
    • Present Value (PV): $79,000
  • Formula: =FV(rate, nper, pmt, [pv], [type])
    • =FV(0.08, 30, -2000, -79000)
    • Result: Higher future value due to annual contributions

Intermediate Functions

Net Present Value (NPV)

  • Scenario: Nike considering opening a new store
  • Inputs:
    • Discount rate: 6%
    • Cash flows: [$-500, $100, $200, $300, $400, $500]
  • Formula: =NPV(rate, value1, [value2], ...) + initial investment
    • =NPV(0.06, B2:B6) + B1
    • Result: Positive NPV suggests the project is profitable

Adjusting for Semi-annual Periods with XNPV

  • Scenario: Cash flows not on annual basis
  • Inputs:
    • Discount rate: 6%
    • Cash flows: [$-500, $100, $200, $300, $400, $500]
    • Dates: [Date1, Date2, ...]
  • Formula: =XNPV(rate, values, dates)
    • =XNPV(0.06, B1:B6, C1:C6)
    • Result: Accurate NPV reflecting actual timing

Internal Rate of Return (IRR)

  • Inputs:
    • Cash flows: [$-500, $100, $200, $300, $400, $500]
  • Formula: =IRR(values)
    • =IRR(B1:B6)
    • Result: 6.7% (IRR > Discount Rate, proceed with the project)

Adjusting for Dates with XIRR

  • Inputs:
    • Cash flows: [$-500, $100, $200, $300, $400, $500]
    • Dates: [Date1, Date2, ...]
  • Formula: =XIRR(values, dates)
    • =XIRR(B1:B6, C1:C6)
    • Result: Slightly higher IRR, reflecting actual timing

Advanced Functions

Loan Payment Schedule

  • Scenario: Loan of $500,000 for 5 years at 7%
  • Inputs:
    • Loan amount: $500,000
    • Annual interest rate: 7%
    • Loan term: 5 years
  • Monthly Calculations:
    1. Monthly Interest Rate: Annual rate / 12
    2. Number of Payments: Loan term * 12
    3. Monthly Payment: =PMT(rate, nper, pv)
    • =PMT(0.07/12, 5*12, -500000)
    • Result: ~$9,000
    1. Interest Payment: =IPMT(rate, per, nper, pv)
    • =IPMT(0.07/12, 1, 5*12, -500000)
    1. Principal Payment: =PPMT(rate, per, nper, pv)
    • =PPMT(0.07/12, 1, 5*12, -500000)
    1. Remaining Balance: Initial loan - sum of principal payments
  • Creating a Full Repayment Schedule:
    • Use SEQUENCE to generate payment periods from 1 to 60
    • Fill out payments, interest, principal, and remaining balance for each month
    • Verify balance is zero at the end of the term

Sponsor: Nordpass

  • Features:
    • Secure password management
    • Sharing sensitive data securely
    • Custom password generator
    • Password health checker
    • Easily manage employee access
  • Offer: 3-month free trial with code Kenji

For more on Excel financial functions, check out the related videos and resources.

Full transcript