Scribd Logo
Upload

Welcome to Scribd!

  • 29 views

    2023 Spring Corporate Finance Week 4 Excel File

    Uploaded by

    Mohamed Radwan
    The document provides examples of calculating future and present values using the time value of money formula. Example 1 shows $1000 growing to $1104.94 after 2 years at 5% annual interest compounded monthly. The total interest earned is $104.94, which exceeds the simple interest of $100 due to compounding. Example 2 calculates a present value of $1644.34 deposited 7 years ago to reach $2500 at 6% annual interest compounded monthly. Example 3 finds it would take 24.56 years for $3000 to grow to $8000 at 4% interest compounded monthly.

    Copyright:

    © All Rights Reserved
    Download as ODS, PDF, TXT or read online from Scribd

    2023 Spring Corporate Finance Week 4 Excel File

    Uploaded by

    Mohamed Radwan
    29 views9 pages
    The document provides examples of calculating future and present values using the time value of money formula. Example 1 shows $1000 growing to $1104.94 after 2 years at 5% annual interest compounded monthly. The total interest earned is $104.94, which exceeds the simple interest of $100 due to compounding. Example 2 calculates a present value of $1644.34 deposited 7 years ago to reach $2500 at 6% annual interest compounded monthly. Example 3 finds it would take 24.56 years for $3000 to grow to $8000 at 4% interest compounded monthly.

    Original Description:

    j

    Original Title

    2023 Spring Corporate Finance week 4 Excel file

    Copyright

    © © All Rights Reserved

    Available Formats

    ODS, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document provides examples of calculating future and present values using the time value of money formula. Example 1 shows $1000 growing to $1104.94 after 2 years at 5% annual interest compounded monthly. The total interest earned is $104.94, which exceeds the simple interest of $100 due to compounding. Example 2 calculates a present value of $1644.34 deposited 7 years ago to reach $2500 at 6% annual interest compounded monthly. Example 3 finds it would take 24.56 years for $3000 to grow to $8000 at 4% interest compounded monthly.

    Copyright:

    © All Rights Reserved
    Download as ODS, PDF, TXT or read online from Scribd
    Download as ods, pdf, or txt
    29 views9 pages

    2023 Spring Corporate Finance Week 4 Excel File

    Uploaded by

    Mohamed Radwan
    The document provides examples of calculating future and present values using the time value of money formula. Example 1 shows $1000 growing to $1104.94 after 2 years at 5% annual interest compounded monthly. The total interest earned is $104.94, which exceeds the simple interest of $100 due to compounding. Example 2 calculates a present value of $1644.34 deposited 7 years ago to reach $2500 at 6% annual interest compounded monthly. Example 3 finds it would take 24.56 years for $3000 to grow to $8000 at 4% interest compounded monthly.

    Copyright:

    © All Rights Reserved
    Download as ODS, PDF, TXT or read online from Scribd
    Download as ods, pdf, or txt
    of 9

    TIME VALUE OF MONEY (CONTINUED)

    FV=PV(1+I)^N

    Example 1
    How much will $1000 deposited into a savings account with 5% APY, compounded monthly
    will grow to after 2 years?

    PV = -1000
    I = 5% / 12 = 0.004166667
    PMT =
    N = 2*12= 24
    FV= ? = PV(1+I)^N = 1000(1+0,42%)^24 = 1104.9413356
    FV = ? Using excel = 1,104.94
    For now, use "0" for type.

    Time line: I = 5% APY, compounded monthly


    PERIOD 1 24

    CASH FLOW PV = -1000 FV = 1104,94

    Example 2
    Today's balance in a savings account is equal to $2500. The account pays an annual interest of 6%, compounded
    monthly. How much was deposited into the account 7 years ago?

    PMT =
    FV = 2500
    N = 7 * 12 = 84
    I = 6% /12 = 0.005
    PV = ? =
    FV=PV(1+I)^N
    PV = FV / (1+I)^N = 2500 / (1+0,5%)^84 = 1644.34
    OR PV = PV function in Excel = -1,644.34

    Time line: I = 6% APY, compounded monthly


    PERIOD 1 84

    CASH FLOW PV = ? = -1644,34 FV = 2500

    Example 3
    How long would it take for $3000 to grow to $8000 if deposited into a savings account with an annual interest
    rate of 4%, compounded monthly?

    PMT =
    FV = 8000
    PV = -3000
    I = 4% / 12 = 0.33% per month
    N = ? = using a formula

    FV=PV(1+I)^N
    FV/PV = (1+I)^N
    ln (FV/PV) = N*ln(1+I)
    N= ln (FV/PV) / ln (1+I) = ln (8000/3000) / ln (1+0,33%) = 294.7389185 periods (months) / 12 =
    N = using Excel formula = nper() = 294.73891853 periods (months) / 12 = 24.56157654

    Time line: I = 4% APY, compounded monthly


    PERIOD 1 N = ? = 294,74 periods (months)

    CASH FLOW PV = 3000 FV = 8000

    Example 4
    What is the annual interest rate on a savings account, compounded monthly if an initial deposit of $1000 3 years ago
    has now grown to $1500?

    PMT =
    FV = 1500
    PV = -1000
    N = 3 * 12 = 36
    I = ? = using formula
    FV=PV(1+I)^N
    FV/PV=(1+I)^N
    (FV/PV)^(1/N) = 1+I
    I = ((FV/PV)^(1/N) ) -1 = (1500/1000)^(1/36) - 1 = 1.13% per period x 12 =

    I = using excel = RATE() = 1.13% per period x 12 = 13.59% per year

    Time line: I = ? APY, compounded monthly


    PERIOD 1 N = 36

    CASH FLOW PV = -1000 FV = 1500

    Example 5
    How much money would one be able to save until retirement if the equivalent of one cup of coffee per day ($3)
    were to be invested into something that mimics the US stock market, close to 10% a year?
    There are 40 years left until retirement.

    PMT = 3 * 365 = -1095


    PV =
    I= 10%
    N= 40
    FV = ? Using excel = 484,638.85

    (THIS IS CALLED AN ORDINARY ANNUITY)


    (ANNUITY IS OPPOSITVE OF SINGLE CASH FLOW)
    Time line: I = 10% APY, compounded annually
    PERIOD 1 N = 40

    CASH FLOW PV = 0 PMT = -1095 PMT = -1095


    FV = ?
    (AN ANNUITY DUE WOULD LOOK LIKE THIS:)
    Time line: I = 10% APY, compounded annually
    PERIOD 1 N = 40

    CASH FLOW PMT = -1095 PMT = -1095 FV = ?


    PV = 0

    Example 6
    Michael borrows $10000 from a local bank at 4% a year, with equal monthly payments for 2 years. What is the amount
    equal monthly payment?

    PV = 10000
    FV = (it means that by the end of year 2, we will have completely paid back the bank)
    I = 4% /12 = 0.003333333 per period
    N = 2 *12 = 24 periods
    PMT = ? = PMT () = -434.24922 per month

    Let's build an amortization schedule for this loan.

    Beginning Principal part Interest part Ending


    Period balance Payment of payment of payment balance
    1 10000 434.25 400.91666667 33.33333333 9599.083333
    2 9599.08 434.25 402.25 32.00 9196.83
    3 9196.83 434.25 403.59 30.66 8793.24
    4 8793.24 434.25 404.94 29.31 8388.30
    5 8388.30 434.25 406.29 27.96 7982.01
    6 7982.01 434.25 407.64 26.61 7574.36
    7 7574.36 434.25 409.00 25.25 7165.36
    8 7165.36 434.25 410.37 23.88 6755.00
    9 6755.00 434.25 411.73 22.52 6343.26
    10 6343.26 434.25 413.11 21.14 5930.16
    11 5930.16 434.25 414.48 19.77 5515.68
    12 5515.68 434.25 415.86 18.39 5099.81
    13 5099.81 434.25 417.25 17.00 4682.56
    14 4682.56 434.25 418.64 15.61 4263.92
    15 4263.92 434.25 420.04 14.21 3843.88
    16 3843.88 434.25 421.44 12.81 3422.44
    17 3422.44 434.25 422.84 11.41 2999.60
    18 2999.60 434.25 424.25 10.00 2575.35
    19 2575.35 434.25 425.67 8.58 2149.69
    20 2149.69 434.25 427.08 7.17 1722.60
    21 1722.60 434.25 428.51 5.74 1294.09
    22 1294.09 434.25 429.94 4.31 864.16
    23 864.16 434.25 431.37 2.88 432.79
    24 432.79 434.25 432.81 1.44 -0.02
    5% APY, compounded monthly
    24

    FV = 1104,94

    t of 6%, compounded

    6% APY, compounded monthly


    84

    FV = 2500

    an annual interest
    24.5615765442 years
    years

    4% APY, compounded monthly


    ? = 294,74 periods (months)

    FV = 8000

    posit of $1000 3 years ago

    13.59%
    per year

    ? APY, compounded monthly


    N = 36

    FV = 1500

    of coffee per day ($3)


    10% APY, compounded annually
    N = 40

    PMT = -1095
    FV = ?

    10% APY, compounded annually


    N = 40

    FV = ?

    2 years. What is the amount

    id back the bank)


    Example 1
    How much will $1000 deposited into a savings account with 5% APY, compounded monthly
    will grow to after 2 years?

    PV = -1000
    I = 5% / 12 = 0.004166667
    PMT =
    N = 2*12= 24
    FV= ? = PV(1+I)^N = 1000(1+0,42%)^24 = 1104.941336
    FV = ? Using excel = 1,104.94
    For now, use "0" for type.

    Time line: I = 5% APY, compounded monthly


    PERIOD 1 24

    CASH FLOW PV = -1000 FV = 1104,94

    Total intetest = 1104.94 - 1000 = 104.94


    Simple interest = 1000*5%/12 *24 = 100

    Compound interest (interest on interest) = 104.94 - 100 =


    pounded monthly

    4.94

    You might also like