ACC301

Introduction to Financial Functions in Excel

  • Excel can be used to solve financial problems using various functions to calculate values related to loans, investments, and savings.

Future Value of an Annuity

Problem Scenario

  • Client Corporation Deposit: $50,000 every quarter for 5 years to purchase machinery.
  • Annual Interest Rate: 4%.
  • Goal: Calculate total amount in account at end of 5 years.

Identification of Key Parameters

  • Type of Question: Future Value of Annuity.
  • Periodic Payment (Pmt): $50,000 (entered as -50,000 because it is an outgoing payment).
  • Number of Periods (N): 4 quarters/year * 5 years = 20 periods.
  • Interest Rate (r): Annual 4% / 4 = 1% per quarter.

Excel Function Usage

  • Use the Future Value (FV) function: 
=FV(rate, nper, pmt, [pv], [type])
  • Input:
    • Rate: 1%
    • Nper: 20
    • Pmt: -50,000
    • PV: 0 (no initial deposit)
    • Type: 0 (payments made at the end of period).

Calculation

  • Formula: 
=FV(0.01, 20, -50000, 0, 0)
  • Result: Amount at the end of 5 years is determined with the FV function, confirming the answer.

Present Value of Future Payments

Problem Scenario

  • Financed Purchase: 10 annual payments of $14,000 (first payment due today).
  • Discount Rate: 9%.
  • Goal: Determine purchase cost of machine (present value of future payments).

Identification of Key Parameters

  • Type of Question: Present Value (PV).
  • Number of Periods (N): 10 (10 annual payments).
  • Interest Rate (r): 9% (annual).
  • Payment (Pmt): -14,000 (debt repayment).
  • Future Value (FV): 0 (no further payments at the end of period).
  • Type: 1 (payments at the beginning of the period).

Excel Function Usage

  • Use the Present Value (PV) function: 
=PV(rate, nper, pmt, [fv], [type])
  • Input:
    • Rate: 9%
    • Nper: 10
    • Pmt: -14,000
    • FV: 0
    • Type: 1.

Calculation

  • Formula: 
=PV(0.09, 10, -14000, 0, 1)
  • Result: Present value of the machine purchase cost calculated.

Future Value of a Sum with Regular Deposits

Problem Scenario

  • Bonds Issued: $1,000,000 due in 10 years, with deposits beginning in one year.
  • Interest Rate: 5%.
  • Goal: Determine annual deposit amount to reach $1 million.

Identification of Key Parameters

  • Type of Question: Future Value (FV).
  • Interest Rate (r): 5%.
  • Number of Periods (N): 10 years.
  • Future Value (FV): $1,000,000 (goal).
  • Present Value (PV): 0 (initial deposit).
  • Type: 0 (end of the period).

Excel Function Usage

  • Use the PMT function to determine regular deposits: 
=PMT(rate, nper, pv, [fv], [type])
  • Input:
    • Rate: 5%
    • Nper: 10
    • PV: 0
    • FV: -$1,000,000 (negative because it's an amount that needs to be reached).
    • Type: 0.

Calculation

  • Formula: 
=PMT(0.05, 10, 0, -1000000, 0)
  • Result: Annual deposit amount needed to reach the goal at the desired future value.

Withdrawals from an Investment Fund

Problem Scenario

  • Initial Investment: $60,000 on 01/01/2014.
  • Withdrawals: 10 annual payments starting 01/01/2029.
  • Interest Rate: 5%.
  • Goal: Determine payment amount of each withdrawal.

Steps for Calculation

Step 1: Future Value Calculation
  • Determine future value of $60,000 after 15 years:
  • Parameters:
    • Rate: 5%
    • Nper: 15
    • PV: 60,000
    • Pmt: 0.
  • Formula: 
=FV(0.05, 15, 0, -60000, 1)
  • Result: Future value available for withdrawals on 01/01/2029.
Step 2: Annual Withdrawal Calculation
  • Determine annual withdrawal amount
  • Parameters:
    • Rate: 5%
    • Nper: 10 (for 10 withdrawals)
    • PV: (result from previous step)
    • FV: 0
    • Type: 1 (beginning of the period).
  • Formula: 
=PMT(0.05, 10, -[future_value], 0, 1)
  • Result: Amount of each withdrawal computed.

Present Value of Deferred Payments for College Tuition

Problem Scenario

  • Loan Amount: $40,000 payments required after 4-year study.
  • Interest Rate: 8%.
  • Goal: Calculate the present value of the tuition.

Steps for Calculation

Step 1: Present Value of Future Payments
  • Parameters:
    • Rate: 8%
    • Nper: 4
    • Pmt: -40,000
    • FV: 0
    • Type: 1 (payment at beginning).
  • Formula: 
=PV(0.08, 4, -40000, 0, 1)
Step 2: Present Value of this Total Amount Today
  • Parameters:
    • Rate: 8%
    • Nper: 4
    • Pmt: 0
    • FV: (result from previous calculation)
    • Type: 0 (payments at end).
  • Formula: 
=PV(0.08, 4, 0, [previous_result], 0)

Evaluating Lottery Options for Present Value

Problem Scenario

  • Lottery Choices: $25,000,000 annually for 20 years versus $311,000,000 today.
  • Goal: Determine the interest rate that equates both options.

Calculation

  • Set up an equation where the present value of the annuity equals the lump sum:
  • Use the RATE function: 
=RATE(nper, pmt, pv, fv, type)
  • Input:
    • Nper: 20
    • Pmt: -25,000,000
    • PV: 311,000,000 (one-time payment)
    • FV: 0
    • Type: 0.
  • Formula: 
=RATE(20, -25000000, 311000000, 0, 0)
  • Result: Approximately 5% interest rate that equates both options.

Calculating Savings Timeframe for Repayment

Problem Scenario

  • Loan Amount: $90,000 owed to parents.
  • Annual Deposit: $6,200.
  • Interest Rate: 6%.
  • Goal: Determine how long it takes to pay back the loan.

Calculation

  • Use the NPER function: 
=NPER(rate, pmt, pv, fv, type)
  • Input:
    • Rate: 6%
    • Pmt: -6,200
    • PV: 0
    • FV: 90,000
    • Type: 0 (end of period).
  • Formula: 
=NPER(0.06, -6200, 0, 90000, 0)
  • Result: Approximately 11 years required to accumulate the necessary funds for repayment.