wn.edu/d21/e/content/2862019/viewContent/21825250/View * B C 1 Excel Project 1 6/22/2020 12:52 Name: 2 3 4 5 6 7 8 Complete the yellow cells using the function called for in each problem. All answers must be positive numbers. All formulas MUST cell referencing or no credit will be given! de IV of $15.5 metrd to raro 75% a year. Answer this sting the rain 10 111 12 N PV 7.5% $15.500 15 Wirard EV: 16 17 Net In the wizard's mere for PMI because there we payments 18 19 Ohrew the FV changat, and 20% fu 0. 1. 2. 3. 4. war. 20 21 Years (my Interest Rate (110) 59 0 1 26 2 27 3 4 5 30 FV 512 510 31 + 0 1 13 View all Download 100% PSC FS F8 FO F10 F11 * % 5 & 7 4 CO 8 9 0 tztown.edu/d2l/le/content/2862019/viewcontent/21825250/View Future Value 33 34 FV a function of and rate 35 $12 36 $10 37 $8 38 39 56 40 $4 1 41 $2 42 43 50 44 45 Years 46. Find the PV of $15.500 due in 5 years if the discount rate is 7.5%. Again, work the problem 47 using the function wirard 48 49 Inputs: 5 50 7.5% 51 FV = $15.500 52 53 Wizard (PV): 54 55 Note: In the Ward's mem, ve zero for PMT because there are no periodic payments. 56 57 58 4. A security has a cost of $15,000 and will return $20,150 after years. What rate of return does the 59 Security provide? 60 61 Inputs: FV 520,150 62 PY - $15,000 0 t 1 33 Views Text A 100% utztown.edu/d21/le/content/2862019/viewContent/21825250/View A B c D E G ? 64 8 65 66 Wirard (Rates 67 68 Notes Use zero for PMT since there are ne periodic payments. Note that the PV is given a 69 negative ion because it is an outflow cost to buy the security). 70 71 72. Seppese California's population is 30 million people, and its population is expected to grow by 4% 73 per year. How long would it take for the population to double? 74 75 Inputs FV 76 PV 30 77 1 growth rate 4% 78 N- 2 79 80 Wizard (NPER): Years to double 81 82 BL Find the PV of an anity that pays $11,000 at the end of each of the next 5 years of the Interest rate is 15%. Then fint of the annuity 85 36 Input PMT $11,000 87 N 5 88 15% 39 90 PV: Use function ward (PV) PV 91 92 FV: the function wizard (FV) 93 95. How would the PV and IV of the annuity change if it were an annuity charather than an ordinary 96 97 98 Use the "typ" box in the function wizard (mitted or for end of period and one for mit der 99 100 PV aliyefumtion wizard (PV) 101 t 1 133 View as Text Download 100% an F5 F8 F10 F11 u/d21/le/content/2862019/viewContent/21825250/View 94 95 How would the PV and FV of the annuity change if it were an annuity due rather than an ordinary 96 annuity? 97 98 Use the "type" box in the function wizard (omitted or zero for end of period, and one for annuity due) 99 100 PV annuity clue (Use function wizard (PV) = 101 102 Exactly the same adjustment is made to find the FV of the annuity due. 103 104 FV annuity due (use function wizard (FV) 105 106 107 h. What would the FV and the PV for problems a and cbe if the interest rate were 7.5% with 108 semiannual compounding rather than 7,5% with annual compounding? 109 110 Part a. FV with semiannual compounding: Orig. Inputs: New Inputs: 111 Inputs: PV $15,500 SI5.500 112 7.50% 3.75% 113 5 10 114 115 Wizard (FV): $0.00 116 117 Part. PV with semiannual compounding: Orig, Inputs: New Inputs: 118 Inputs: FV $15,500 $15,500 119 7.50% -3.75% 120 5 10 121 122 Wiard (PV) S0.00 123 124 125 1 13 View as Te A 100% du/d21/1e/content/2862019/viewContent/21825250/View H B D E F G 126 127 . Find the PV of an investment that makes the following end-of-year payments. The 128 interest rate is 8%. 129 130 Year Payment 131 1 $100 132 2 $200 133 3 $400 134 135 Rate - 8% 136 137 To find the PV, use the NPV function: PV 138 139 140 141 . Suppose you bought a house and took out a mortgage for $150,000. The interest rate is 8%, and 142 you must amortize the loan over 10 years with equal end-of-year payments. Set up an amortization 143 schedule that shows the annual payments and the amount of each payment that goes to pay off the 144 principal and the amount that constitutes interest expense to the borrower and interest income to 145 the lender. 146 147 Original amount of mortgages $150,000 148 Term of mortgage: 10 149 Interest rates 896 150 151 Annual payment (use PMT function): 152 153 Observe the amortization of this mortgage 154 Year Ber Amt. Pmt Interest Principal End. Bal. 155 1 $150,000.00 $0.00 $ 12,000.00 $12,000.00 $162,000.00 2 $162,000.00 $0.00 $ 12,960.00 $12.960.00 $174,960.00 157 3 $174.960.00 $0.00 $ 13,996.80 $13,996.80 $188,956.80 158 4 $188,956.80 $0.00 $ 15,116.54 $15,116.54 $204,073.34 159 5 S204,073.34 $0.00 $ 16,325.87 $16,325.87 $220,399.21 160 6 $220,399.21 S0.00 $ 17,631.94 -$17,631.94 $238,031.15 161 7 $238,031.15 S0.00 S 19,042.49 $19,042.49 $257,073.64 162 $257,073.64 $0.00 $ 20,565.89 -$20,565.89 $277.639.53 163 9 5277,639.53 S0.00 $ 22,211.16 -$22,211.16 $299,850.69 164 10 $299,850.69 $0.00 $ 23,988.06 $23,988.06 $323,838.75 165 156 + 1 3 Vies so 1009 edu/d21/le/content/2862019/viewContent/21825250/View 140 Tage. 149 Interest rate: 8% 150 Year 156 157 158 159 160 151 Annual payment (use PMT function): 152 153 Observe the amortization of this mortgage 154 Bes. Amt. Pmt Interest Principal End. Bal. 155 1 $150,000.00 $0.00 $ 12,000.00 -S12,000.00 $162,000.00 2 $162,000.00 $0.00 $ 12,960.00 -$12.960.00 $174.960.00 $174,960.00 $0.00 $ 13,996.80 $13,996.80 $188,956.80 4 $188,956.80 $0.00 $ 15,116.54 -$15,116.54 $204,073.34 5 S204,073.34 $0.00 $ 16,325.87 $16,325.87 $220,399.21 6 $220,399.21 $0.00 $ 17,631.94 -$17,631.94 $238,031.15 161 7 $238,031.15 S0.00 $ 19,042.49 -$19,042.49 $257,073.64 162 8 $257,073.64 $0.00 $ 20,565.89 -$20,565.89 $277,639.53 163 9 S277,639.53 $0.00 $ 22,211.16 -$22,211.16 $299,850.69 164 10 $299,850.69 $0.00 $ 23.988.06 -$23,988.06 $323,838.75 165 166 Extensions: This graph shows how the payments are divided between interest and 167 principal repayment over time. 168 169 Breakdown of payments 170 171 530,000.00 172 173 $20,000.00 174 $10,000.00 175 $ 176 177 $(10,000.00) Pricipal Insere 178 S(20,000.00) 179 $(30,000.00) 180 1 5 6 7 8 9 10 181 Years 182 183 184 w 1 13 Vic A 100
