Topic outline

  • Learning Task 1.4 Perform Basic Time Value of Money Calculations

    Prev Next

    • Task

      Complete the questions.

      Learning Outcome

      After completing this task, you should be able to:

      • understand the background to the time value of money concept
      • use basic financial functions in Excel (i.e. =PV, =FV, =PMT, =RATE and =NPER)

      Due

      Midweek 1.

      Notes

      Students commencing any program related to finance may sometimes experience concerns regarding the level of mathematical concepts and equations that they will be expected to understand and use throughout the course. This is a fair concern, but not one that should prevent you from getting involved in the study of personal financial planning.

      As financial planning is comprised of elements of accountancy, economics, finance, law, psychology, regulation, statistics and taxation, it will require some level of numeracy. However, the demands in this introductory course are relatively basic and lean predominantly on simple algebra, ratios, percentages, and statistics commonly covered in the early years of high school.

      Having stated that, we don’t want to overstate the role of mathematics, it is more important that you can logically solve problems and that you learn to use an appropriate tool (in this course we rely on Excel) to solve the more complex mathematical and statistical problems. Thus, where you see mathematics that you feel may be beyond you, your concern should not be with the mathematics per se, rather it should be with the problem being solved.

      You should also feel confident that you will be progressively introduced to material and that your understanding will deepen as you progress with your studies in personal financial planning (and beyond if you choose). You will be introduced and instructed on the core material required to deepen your understanding and introduced to resources that will assist you in this endeavour.

      Suggested Procedure

      1. Explore the use of simple time value of money concepts and methods in Excel by accessing the Simple_Time_Value_of_Money_calculations_in_Excel workbook.
        • You will find a range of the most common PV and FV tools used in Excel and the data (inputs) required for each
        • You can change some of the input values (pressing the Ctrl + Z keys simultaneously will allow you to reverse these changes) and see how this changes the answers given
        • Where you see a coloured triangle in the top right corner of a worksheet cell, note that clicking on this will allow you to read the comment that is embedded there (a hint or explanation)
      2. Use the Excel workbook in conjunction with the worked solutions provided in the text (that involve the discussion of and/or solution to quantitative financial planning problems, as these will highlight the solution method required, e.g. the specific formula in Excel) to complete the following questions:
        1. How many years will it take for your investment to treble in size if it were invested at 9% per annum effective interest?
        2. If inflation is expected to be 3.5% over the following year and you earn $37,000 per year, how much must you earn next year to keep up with inflation?
        3. You expect to have $2.4 million when you retire in 35 years. Assuming that your expected rate of return over the next 35 years is 5.25% per annum, how much is this sum worth in today’s dollars?
        4. Your annual income is $40,000. The bank will allow you to pay 25% of your income in mortgage repayments. If the interest rate on a mortgage is 7.5% per annum compounded monthly, how much can you borrow if the term of the mortgage is 20 years?
        5. A loan of $50,000 is to be repaid over 8 years by way of equal monthly payments at a fixed interest rate of 8% per annum compounded monthly. What (i) is the amount of the equal monthly payment and (ii) the amount of outstanding principal that will remain after twelve payments have been made?
        6. You have the right to receive 6 annual payments of $5,000 followed by 8 annual payments of $9,000. If the first payment is to be paid today and your opportunity cost is 9% per annum, what is the today's lump sum equivalent of these cash flows?
      3. Compare your responses with the suggested solutions below.
        1. A standard calculation based on present value (PV) and future value (FV) relationships. Here r is what is known as an effective interest rate (a rate, where interest is paid once a year) and T is the number of years.
          • Using Excel: =NPER(0.09,0,-1,3) = 12.7482
          • Note the need to use CF conventions in Excel (-1 for the PV and 3 for the FV)
          • Using FV tables: Locate FV factors in single CF table equal to around 3, where the interest rate is 9%, then interpolate the number of years. So, for 12 years FV factor is 2.8127 and for 13 years FV factor is 3.0658
          • Difference between the two is 0.2531
          • 2.8127 + 0.2531X = 3 therefore X = 0.7482
          • Add to 12 years to get the answer
        2. A simple future value calculation where we replace the interest rate with the inflation rate.
          • Using Excel: =FV(0.035,1,0,-37000) = $38,295
          • Mathematically: FV = $37,000(1+0.035) = $38,295
        3. A simple PV problem.
          • Using Excel: =PV(0.0525,35,0,-2400000) = $400, 347.93
          • Mathematically: PV = 2400000/(1+0.0525)35 = $400, 347.93
          • Can be done with tables by interpolating between the 5% and 6% PV factors over 35 years for single CFs
        4. Clearly, this is a simple ordinary annuity problem. The problem at hand is to find the PV of the annuity payments that we can make. Given our stated income per year of $40,000 and the bank’s rules, we can make payments of $10,000 per year or $833.33 per month. This allows the borrowing of:
          • Using Excel: =PV(r,T,PMT,0), =PV(0.075/12,20*12,-10000/12,0) = $103,443.34
          • PMT represents the monthly payment, T the number of months (= 20 x 12) and r is the interest rate per month, which is calculated from the annual percentage rate (= 0.075/12)
        5. These are standard annuity problems, (i) requires the solution of the size of payment, while (ii) simply requires the solution of the present value when only seven years remains on the loan.
          • Using Excel:
          • (i) =PMT(0.08/12,8*12,50000,0) = -$706.83
          • (ii) =PV(0.08/12,7*12,706.83,0) = $45,349.69
        6. A complex PV problem involving annuities. You need to draw a timeline and break the problem into three parts: valuing an annuity of $5,000 per year for 5 years, then valuing the second stage of the CFs. To do this, first value the 8 annuity payments of $9,000 at year 5 (the first payment of $9,000 occurs at year 6), then get the PV of this result by using the simple PV factor for 9% over 5 years. Add the two PVs together with the time 0 payment of $5,000 to get the total current value of these cash flows.
          • Using Excel:
          • =PV(0.09,5,-5000,0) = $19,448.26 for the cash flows at years 1 to 5
          • =PV(0.09,8,-9000,0) = $49,813.37 for the year 5 value of the remaining CFs, then =PV(0.09,5,0,-49,813.37) = 32,375.27 to get their year 0 PV
          • Add each of the above PVs together along with the year 0 CF of $5,000 to get $56,823.53  =(19,448.26+32375.27+5000)
          • OR
          • =NPV(0.09,5000,5000,5000,5000,5000,9000,9000,9000,9000,9000,9000,9000,9000)+5000 = $56,823.53
          • (Note that the NPV function assumes that the first CF occurs at time 1)
      4. Post any questions you may have to the Week 1 Q and A forum. A teaching staff member will respond there shortly.
    Prev Next