Part 1 – Single Cash Flow BUS3062 Week 2 Assignment Template Part 1: Time Value of Money...

Question

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 Finance
    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: Finance 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 co
ect 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 recu
ing 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 co
ect 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 co
ect 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 recu
ing 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 co
ect answer is $6, XXXXXXXXXXdetermined 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 co
ect 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 recu
ing 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 co
ect 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 co
ect 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 recu
ing 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 inco
ect.)
    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 co
ect 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 inco
ect.)
    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

cfweek2assignmenttemplate-hs2lt033.xlsx

Mohit · Accepted Answer

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?	150(1+0.07)11
150(1.07)11
150(2.1049)
$ 315.73	This is a Future value calculation, as we have to deposit funds today and calculate return after 11 years. As interest rate is same for 11 years.
Formula, FV= PV(1+r)t
where, FV= Future Value, PV = Present Value
r = Interest Rate and t = Time of investment
	 
	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?	350(1+0.08)(1+0.06)(1+0.055)
350(1.08)(1.06)(1.055)
$ 422.7174	This is a Future value calculation, as we have to deposit funds today and calculate return after 3 years. As interest rate is different in all 3 years.
Formula, FV= PV(1+r1)(1+r2)(1+r3)
where, FV= Future Value, PV = Present Value
r1 = Interest Rate for 1st year, r2= Interest Rate for 2nd year and r3= Interest Rate for 3rd year.
	3. Compute the present value of an $850 payment made in 10 years when the discount rate is 12%.	PV=          850         
                       (1+0.12)10
          =             850     
                        (1.12)10
         =         850      
                    3.8960
                =    $ 218.17	This is a present value calculation problem. We have to compute present value of payment made after 10 years. As discount rate is same for 10 years.
Formula, PV=   FV    
                       (1+r)t
where, PV = Present Value, FV= Future Value, 
r= interest rate and t= time 
	4. What annual rate of return is earned on a $5,000 investment when it grows to $9,500 in five years?	9500 = 5000 (1+r)5
9500/5000 = (1+r)5
1.9 = (1+r)5
13.75%	This is a interest rate calculating problem. As we have present value, future value and time period of investment. Hence Formula is,
FV = PV (1+r)t
where, FV= Future Value, PV= Present Value, r= interest rate and t= time period of investment.
From, Future value table, we can see @13% interest rate, compounding of $1 for 5 years is 1.8424 and 1.9254 from 14% interest rate.
1.90 is in middle of 13% and 14%. We have calculated manually between 13% and 14%.
	5. What is the rate of interest if your money doubles every 6 years? This is also known as Rule of 72.	 6 =              72             
                            Interest Rate
Interest Rate = 12	As per Rule of 72,
Years to double =          72         
                              Interest Rate
	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.	Compouding on Annual basis,
FV = 150(1+0.07)11 =  $315.73
Compouding on Monthly basis,
FV = 150 1+0.07)    132 = $ 323.24
                          12	Taking Problem Number 1,
We need to calculate Future value of an amount deposited now, when complounding on annual basis and on annual basis.
Compounding Annually,
Formula, FV= PV(1+r)t
Compounding Monthly,
Formula, FV= PV  1+r     nt
                                n   
where, FV= Future Value, PV = Present Value, r=Interest Rate, t= Time for investment.
	Compounded Yearly	315.735
	Compounded Monthly	323.235
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%?	FV Annuity Due =C×  (1+i)n−1     ×(1+i)
                                            i
where, C= Annuity payment, i= Interest rate, n= Number of years	1000*  (1+0.09)5 - 1     *(1+0.09)
     0.09
=$6523.33		]
	2. What is the present value of a $800 annuity payment over six years if interest rates are 10%?	FV Annuity Due =C×  (1+i)-n−1     ×(1+i)
                                            i
where, C= Annuity payment, i= Interest rate, n= Number of years	800*  (1+0.10)-6 - 1     *(1+0.10)
     0.10
=$3832.63
	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?	Please refer Part-2 (3)	$ 859
	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?	FV Annuity Due =C×  (1+i)n−1     ×(1+i)
                                            i
where, C= Annuity payment, i= Interest rate, n= Number of years	200*  (1+0.01)300 - 1     *(1+0.01)
     0.01
=$379527.02
	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%.	We have to make a full table showing inflows and outflows year to year.
Please refer Part-2 (5)	$24933
	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. 	Shorter Amortization Period:
you will have higher monthly payments, but you will also save considerably on interest over the life of the loan. Also, interest rates on shorter loans are typically lower than those for longer terms. This is a good strategy if you can comfortably meet the higher monthly payments without undue hardship.
an amortization schedule is a complete timeline of periodic loan payments, showing the amount of principal and the amount of interest that comprise each payment until the loan is paid off at the end of its term. It also usually tracks the size of the balance. Amortization schedules demonstrate how, early in the life of the loan, the majority of each payment is what is owed in interest; later in the schedule, the majority of each payment covers the loan's principal.
Part-2 (3)
	Starting from 01-01-2020
	Period	O/s Balance	Interest	Monthly Payment	Principal Payment
	1	160,000.00	666.67	859	192.3333333333
	2	159,807.67	665.87	859	193.1347222222
	3	159,614.53	665.06	859	193.9394502315
	4	159,420.59	664.25	859	194.7475312741
	5	159,225.84	663.44	859	195.5589793211
	6	159,030.29	662.63	859	196.3738084016
	7	158,833.91	661.81	859	197.1920326033
	8	158,636.72	660.99	859	198.0136660724
	9	158,438.71	660.16	859	198.8387230144
	10	158,239.87	659.33	859	199.6672176936
	11	158,040.20	658.50	859	200.499164434
	12	157,839.70	657.67	859	201.3345776192
	13	157,638.37	656.83	859	202.1734716926
	14	157,436.19	655.98	859	203.015861158
	15	157,233.18	655.14	859	203.8617605795
	16	157,029.32	654.29	859	204.7111845819
	17	156,824.60	653.44	859	205.564147851
	18	156,619.04	652.58	859	206.4206651337
	19	156,412.62	651.72	859	207.2807512384
	20	156,205.34	650.86	859	208.1444210352
	21	155,997.19	649.99	859	209.0116894562
	22	155,788.18	649.12	859	209.8825714956
	23	155,578.30	648.24	859	210.7570822102
	24	155,367.54	647.36	859	211.6352367194
	25	155,155.91	646.48	859	212.5170502057
	26	154,943.39	645.60	859	213.4025379149
	27	154,729.99	644.71	859	214.2917151562
	28	154,515.70	643.82	859	215.1845973027
	29	154,300.51	642.92	859	216.0811997915
	30	154,084.43	642.02	859	216.9815381239
	31	153,867.45	641.11	859	217.8856278661
	32	153,649.56	640.21	859	218.7934846489
	33	153,430.77	639.29	859	219.7051241683
	34	153,211.07	638.38	859	220.6205621856
	35	152,990.44	637.46	859	221.5398145281
	36	152,768.90	636.54	859	222.4628970886
	37	152,546.44	635.61	859	223.3898258265
	38	152,323.05	634.68	859	224.3206167674
	39	152,098.73	633.74	859	225.2552860039
	40	151,873.48	632.81	859	226.1938496956
	41	151,647.28	631.86	859	227.1363240694
	42	151,420.15	630.92	859	228.0827254196
	43	151,192.06	629.97	859	229.0330701089
	44	150,963.03	629.01	859	229.9873745677
	45	150,733.04	628.05	859	230.945655295
	46	150,502.10	627.09	859	231.9079288588
	47	150,270.19	626.13	859	232.8742118957
	48	150,037.31	625.16	859	233.8445211119
	49	149,803.47	624.18	859	234.8188732832
	50	149,568.65	623.20	859	235.7972852552
	51	149,332.85	622.22	859	236.7797739438
	52	149,096.07	621.23	859	237.7663563352
	53	148,858.31	620.24	859	238.7570494866
	54	148,619.55	619.25	859	239.7518705262
	55	148,379.80	618.25	859	240.7508366533
	56	148,139.05	617.25	859	241.7539651394
	57	147,897.29	616.24	859	242.7612733275
	58	147,654.53	615.23	859	243.772778633
	59	147,410.76	614.21	859	244.788498544
	60	147,165.97	613.19	859	245.8084506213
	61	146,920.16	612.17	859	246.8326524988
	62	146,673.33	611.14	859	247.8611218843
	63	146,425.47	610.11	859	248.8938765588
	64	146,176.58	609.07	859	249.9309343778
	65	145,926.64	608.03	859	250.972313271
	66	145,675.67	606.98	859	252.018031243
	67	145,423.65	605.93	859	253.0681063731
	68	145,170.59	604.88	859	254.1225568164
	69	144,916.46	603.82	859	255.1814008031
	70	144,661.28	602.76	859	256.2446566398
	71	144,405.04	601.69	859	257.3123427091
	72	144,147.73	600.62	859	258.3844774704
	73	143,889.34	599.54	859	259.4610794599
	74	143,629.88	598.46	859	260.5421672909
	75	143,369.34	597.37	859	261.6277596547
	76	143,107.71	596.28	859	262.7178753199
	77	142,844.99	595.19	859	263.8125331337
	78	142,581.18	594.09	859	264.9117520218
	79	142,316.27	592.98	859	266.0155509885
	80	142,050.25	591.88	859	267.1239491177
	81	141,783.13	590.76	859	268.2369655723
	82	141,514.89	589.65	859	269.3546195955
	83	141,245.54	588.52	859	270.4769305105
	84	140,975.06	587.40	859	271.603917721
	85	140,703.46	586.26	859	272.7356007115
	86	140,430.72	585.

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...

Solution

Answer To This Question Is Available To Download

Related Questions & Answers