TVM – SINGLE AND ANNUITY CASH FLOW

Part 1 – Single Cash Flow

BUS3062 Week 2 Assignment Template

Part 1: Time Value of Money Single Cash Flow

Solve the following problems and answer the last question. Example problems can be found on the “Example Single Cash Flow” tab below. Create an appropriate (TVM) formula using the supplied values in the appropriate cell so Excel can calculate the answer.

Calculations

1. How much would be in your savings account in 11 years after depositing $150 today, if the bank pays 7% per year? [Answer here]

2. A deposit of $350 earns the following interest rates: (a) 8% in the first year, (b) 6 in the second year, and (c) 5.5 in the third year. What would be the third year future value? [Answer here]

3. Compute the present value of an $850 payment made in 10 years when the discount rate is 12%. [Answer here]

4. What annual rate of return is earned on a $5,000 investment when it grows to $9,500 in five years? [Answer here]

5. What is the rate of interest if your money doubles every 6 years? This is also known as Rule of 72. [Answer here]

Question

6. Given the same annual interest rate, would you rather have a savings account that paid interest compounded on a monthly basis, or one that compounded interest on an annual basis? Perform the calculation to support your answer. [Answer here]

Part 2 – Annuity Cash Flows

Part 2: Time Value of Money Annuity Cash Flows

Solve the following problems and answer the last question. Example problems can be found on the “Example – Annuity Cash Flow” tab. Create an appropriate (TVM) formula using the supplied values in the appropriate cell so Excel can calculate the answer.

Calculations

1. What is the future value of a $1,000 annuity payment over five years if interest rates are 9%? [Write the formula here] [Answer here]

2. What is the present value of a $800 annuity payment over six years if interest rates are 10%? [Write the formula here] [Answer here]

3. Assume you purchased a house on January 1, 2020 for $200,000. You had made a down payment of 20% on the house and the balance was financed with a 30 year loan at 5% per annum stated APR with monthly payments to be made beginning January 1, 2020. What are your monthly payments? [Write the formula here] [Answer here]

4. Judith has just become eligible to participate in her company’s retirement plan. Her company does not match contributions, but the plan does average an annual return of 12%. Judith is 40 and plans to work to age 65. If she contributes $200 per month, how much will she have in her retirement plan at retirement? [Write the formula here] [Answer here]

5. How much do you have to deposit today so that exactly 10 years from now you can withdraw $10,000 a year for the next five years? Assume an interest rate of 6%. [Write the formula here] [Answer here]

Question

Imagine that a friend tells you that you should not rush to pay off your mortgage early because you will lose out on the interest tax deductions you are getting. Discuss the role of amortization of mortgages in your analysis of the issue. [Answer here]

Example – Single Cash Flow

BUS3062 Fundamentals of

Time Value of Money (TVM) – Analyzing Single Cash Flows

Practice Problem Worksheet Using Excel Formulas

Make sure to read and study Chapter 4, “Time Value of Money 1: Analyzing Single Cash Flows,” from your M: text before reviewing the following problems and solutions.

Be advised that annual compounding is used in each of these practice problems, unless more frequent compounding is otherwise specifically disclosed.

Future Value

Future Value Problem 1) Here is a simple future value problem:

How much would you have in your savings account after 5 years if you deposited $1,000 today and it earned 6% interest per year? The correct answer is $1,338.23 determined below.

Solution: First, identify this is a future value problem (this is determined because we are asked how much money we will have (in the future.) That is our clue that this is a FV problem.

Next identify the known variables:

The interest rate: I = 6% (or 0.06 expressed as a decimal)

The number of time periods: N = 5

The present value: PV = 1,000 (note that you will enter PV as a negative number below)

Next using the Excel, Formulas, Financial, FV formula that reads as follows:

FV = (Int rate, Number of periods, next enter 0 because there are no recurring payments for this type of problem, enter the Present Value as a negative number)

inputting the numbers into the Excel FV formula in the exact order prescribed by the formula (click on Cell B32 to see the contents of the cell as inputted):

FV = $1,338.23

Future Value Problem 2

How much would you have after 10 years if you deposited $1,500 today and it earned 5% interest per year? The correct answer is $2,443.34 determined below.

FV = $2,443.34

Present Value

Present Value Problem 1

How much would you need to deposit today if you wanted to end up with $10,000 in your savings account in 8 years and the deposit would earned 7% interest per year? The correct answer is $5,820.09 determined below.

Solution: First, identify this is a present value problem (this is determined because we are asked how much money we need to deposit (today.) That is our clue that this is a PV problem.

Next identify the known variables:

The interest rate: I = 7% (or 0.07 expressed as a decimal)

The number of time periods in years: N =8

The future value: FV = 10,000

Next using the Excel, Formulas, Financial, PV formula that reads as follows:

PV = (Int rate, Number of periods, next enter 0 because there are no recurring payments for this type of problem, enter the Future Value)

Inputting the numbers into the Excel PV formula in the exact order prescribed by the formula (click on Cell B68 to see the contents of the cell as inputted):

PV = ($5,820.09) (Note that PV is solved showing it as a negative number. This is because of the formula that is used to solve the equation and represents that $5,820.09 must be paid out (an outflow) in order to receive the 10,000 (inflow) amount in the future. Even though the Excel mathematical equation solves the PV as a negative number, one should simply refer to the PV as $5,820.09 without reference to it as a negative number.)

Present Value Problem 2

What is the present value of $15,000 payment planned to be made in 12 years when the discount rate is 7%? The correct answer is $6, 660.18 determined below.

PV = ($6,660.18)

Time Period

Solving for Time:

Problem 1: Here is a simple time value of money problem solving for number of periods:

How long would it take a deposit of $2,500 to grow to $3,500 assuming it earned 4.5% annual interest? The correct answer is 7.64 years determined below.

Solution: First, identify this is a time value of money problem attempting to solve for time. (Such time period problems are usually easy to identify and often state “how long will it take.”)

Next identify the known variables:

The interest rate: I = 4.5% (or 0.045 expressed as a decimal)

The present value: PV = 2,500 (note you will enter PV as a negative number below)

The future value: FV = 3,500

Next using the Excel, Formulas, Financial, NPER formula that reads as follows:

NPER = (Int rate, next enter 0 because there are no recurring payments for this type of problem, enter the Present Value amount as a negative number, enter the future value amount)

inputting the numbers into the Excel NPER formula in the exact order prescribed by the formula (click on Cell B109 to see the contents of the cell as inputted):

NPER = 7.64 years

Problem 2

How long would it take a deposit of $150 to grow to $300 (another way of saying this would be “how long would it take any deposit to double”) assuming it earned 7.5% annual interest? The correct answer is 9.58 years determined below.

NPER = 9.58 years

Annual Rate of Return

Solving for Interest Rate

Problem 1: Here is a simple problem solving for interest rate (annual rate of return):

What interest rate must you earn to turn your $3,000 deposit into $4,000 within 6 years? The correct answer is 4.91% determined below.

Solution: First, identify this is a time value of money problem attempting to solve for a requested interest rate. Such interest rate problems often state “what rate of interest must be earned…” or similar wording.

Next identify the known variables:

The number of time periods in years: N = 6

The present value: PV = 3,000 (note that you will enter PV as a negative number below)

The future value: FV = 4,000

Next using the Excel, Formulas, Financial, RATE formula that reads as follows:

RATE = (Number of time periods, next enter 0 since there are no recurring payments for this problem, enter the Present Value amount as a negative number, enter the Future Value amt.)

inputting the numbers into the Excel RATE formula in the exact order prescribed by the formula (click on Cell B148 to see the contents of the cell as inputted):

RATE = 4.91% (Make sure you change the number of decimals in the formula cell so you show at least 4 decimal places as is shown to the left, otherwise, your response would show 5% and that is not sufficiently exact and would be marked as incorrect.)

RATE 2) Here is a simple time value of money solving for interest rate practice problem:

If you had $10,000 and needed it to grow to $12,000 within 3 years, what annual rate of return would you need to receive? The correct answer is 6.27% determined below.

NPER = 6.27% (Make sure you change the number of decimals in the formula cell so you show at least 4 decimal places as is shown to the left, otherwise, your response would show 6% and that is not sufficiently exact and would be marked as incorrect.)

Differing Interest Rates

Solving for Future Value with differing rates of interest:

Problem 1: Here is a simple FV problem solving with differing interest rates:

A $1,500 deposit you make today is expected to earn 3% the first year, 4% the second year, 4.5% the third year, and 5% the fourth year. How much would you have at the end of the fourth year? The correct answer is 1,763.06 determined below.

Solution: First, identify this is a future value problem with differing rates of interest.

Next, the answer is calculated by taking the original deposit and multiplying it by (1+ Interest Rate) for each annual period’s interest rate, algebraically shown as follows: ($1,500 original deposit x 1.03 x 1.04 x 1.045 x 1.05 = $1,763.06 (also click on Cell B181 to see the contents of the cell as solved algebraically without the use of an Excel Formula function):

Answer = $ 1,763.06

Problem 2

A deposit of $850 earns the following interest rates: 5% the first year, 5.5% the second year, and 6% the third year. What would be third year future value? The correct answer is $998.08 determined below.

Answer = $ 998.08

Compounding Frequency

Compounding Frequency:

Each of the above problems assumes interest is compounded on an annual basis. However, in actuality, many financial obligations are compounded more frequently, such as bond interest (normally semi-annual frequency as we will see in Week 3), many bank CDs (quarterly, or more frequent compounding), home mortgage and car financing (monthly frequency.) Review the section, “Compounding Frequency,” in Chapter 5 of your M: text for a further discussion of this topic.

Example Annuity Cash Flow

BUS3062 Fundamentals of

Time Value of Money (TVM) – Analyzing Annuity Cash Flows

Practice Problem Worksheet Using Excel Formulas

It is highly recommended you first read and study Chapter 4, “Time Value of Money 1: Analyzing Single Cash Flows,” and Chapter 5, “Time Value of Money 2: Analyzing Annuity Cash Flows,” from your M: text before reviewing the following problems and solutions. One must understand the TVM theory before being able to solve the TVM problems.

Future Value of an Annuity Be advised that annual compounding is used in each of these practice problems, unless more frequent compounding is otherwise specifically disclosed.

Solving for the Future Value of an Annuity:

Problem 1: Here is a simple future value of an annuity problem:

What is the future value of a $1,000 annual annuity payment over 10 years if interest rates are 5.5%. The correct answer is $12,875.35 determined below.

Solution: First, identify this is a future value of an annuity problem (this is determined because we are asked how much money we will have in the future and there is an annuity payment made each year for the same amount (that is what makes it an annuity.)

Next identify the known variables:

The interest rate: I = 5.5% (or 0.055 expressed as a decimal)

The number of time periods: N = 10

The annuity payment is $1,000: PMT = $1,000 (note you will enter PMT as a negative number)

Present value: PV=0 (There is no beginning PV amount. Enter “0” in the PV field in the formula.)

Next using the Excel, Formulas, Financial, FV formula that reads as follows:

FV = (Int rate, Number of periods, annuity payment {enter payment as a negative number}, Present Value {enter 0 if there is no specific PV amount given})

Inputting the numbers into the Excel FV formula in the exact order prescribed by the formula (click on Cell B36 to see the contents of the cell as inputted):

FV = $12,875.35

Problem 2

What is the future value of a $100 annual annuity payment made over 20 years if the interest rate is 4.5%. The correct answer is $3,137.14 determined below.

FV = $3,137.14

Present Value of an Annuity

Solving for the Present Value of an Annuity:

Problem 1: Here is a simple present value of an annuity problem:

What is the present value of a $1,500 annuity payment over 8 years if interest rates are 6.5%. The correct answer is $9,133.13 determined below.

Solution: First, identify this is a present value of an annuity problem (this is determined because we are asked how much money we need now and there is an annuity payment incurred each year for the same amount (that is what makes it an annuity.)

Next identify the known variables:

The interest rate: I = 6.5% (or 0.065 expressed as a decimal)

The number of time periods: N = 8

The annuity payment is $1,500: PMT = $1,500 (note you will enter PMT as a negative number)

The future value: FV = 0 (There is no ending FV amount. Enter “0” in the FV field in the formula.)

Next using the Excel, Formulas, Financial, PV formula that reads as follows:

PV = (Int rate, Number of periods, annuity payment {enter payment as a negative number}, Future Value {enter 0 since there is no FV amount given})

Inputting the numbers into the Excel PV formula in the exact order prescribed by the formula (click on Cell B73 to see the contents of the cell as inputted):

PV = $9,133.13

Problem 2

What is the present value of a $1,100 annuity payment over 3 years if interest rates are 8.5%. The correct answer is $2,809.42 determined below.

PV = $2,809.42

Compounding Frequency

Each of the above problems assumes interest is compounded on an annual basis. However, in actuality, many financial obligations are compounded more frequently, such as bond interest (normally semi-annual frequency as we will see in Week 3), many bank CDs (quarterly, or more frequent compounding), home mortgage and car financing (monthly frequency.) Review the section “Compounding Frequency,” in Chapter 5 of your M: text for a further discussion of this topic.