Chapter 4 Spreadsheet on the problem (C04)Amortization table2. The chart shows the total payment, pay to the interests of capital and the payment of the loan for 20 years, is displayed when you click on the tab called CHART at the bottom of the spreadsheet. To return to the worksheet, click the tab called C04 at the bottom of the sheet graphic work.3. Start by organizing the worksheet by placing the line 21 at the top of the screen. This will allow you to simultaneously view the input data and amortization. Then place the cursor in a cell in the input data, enter new data and observe changes in the amortization table. Also, work the problem to a term of 20 years and interest rates of 3 and 25%, see chart and note the difference in the amount of payments and interest between the fertilizer and capital.4. F25..F44 cells contain the present value of each annual payment discounted at the appropriate interest rate. By changing the interest rate, you can see what happens to each discounted payment. The sum of this range is equal to the original loan amount.DATA ENTRY: KEY FINDINGS:Loan amount 20,000 I pay 2,037.04Interest rate 8.00%Number of years twentyData generated by the model:Amortization table:crediting crediting Balance PV ofYear I pay interests capital payments1 2,037.04 1,600.00 437.04 19562.96 1,886.152 2,037.04 1,565.04 472.01 19090.95 1,746.443 2,037.04 1,527.28 509.77 18581.18 1,617.074 2,037.04 1,486.49 550.55 18030.63 1,497.295 2,037.04 1,442.45 594.59 17436.04 1,386.386 2,037.04 1,394.88 642.16 16793.87 1,283.687 2,037.04 1,343.51 693.53 16100.34 1,188.608 2,037.04 1,288.03 749.02 15351.32 1,100.559 2,037.04 1,228.11 808.94 14542.39 1,019.0310 2,037.04 1,163.39 873.65 13668.73 943.55eleven 2,037.04 1,093.50 943.55 12725.19 873.6512 2,037.04 1,018.01 1,019.03 11706.16 808.9413 2,037.04 936.49 1,100.55 10605.61 749.0214 2,037.04 848.45 1,188.60 9,417.01 693.53fifteen 2,037.04 753.36 1,283.68 8,133.33 642.1616 2,037.04 650.67 1,386.38 6,746.95 594.5917 2,037.04 539.76 1,497.29 5,249.66 550.5518 2,037.04 419.97 1,617.07 3,632.59 509.7719 2,037.04 290.61 1,746.44 1,886.15 472.01twenty 2,037.04 150.89 1,886.15 0.00 437.0440740.88 20740.88 20,000.00 20,000.00We have previously entered data in this file to the base caseeach model, and these are designed for the analysis of preceding partsof the problem. You should only enter the details of each of the partiesthe remaining problem-we point out in each problem, the partiesto be performed using the spreadsheet. However, there are severalthings to consider before entering the model:1. The input data are entered in specific cells in the sectionDATA ENTRY. When an input is changed, the modelDATA ENTRY. When an input is changed, the modelDATA ENTRY. When an input is changed, the modelDATA ENTRY. When an input is changed, the model2. The key results are displayed on the right sectionDATA ENTRY or immediately below it. This distribution allows youmodify an input and immediately see how the change affects the resultsmodel. This is quite useful in a sensitivity analysis.3. The input data that can be modified differ from those who should notmodify. The data that can be changed are highlighted in color (blue) toUnlike other data are black.4. All percentages must be entered as decimal. Monetary unitsand other numbers should be entered without signs of pesos or dollars without commas.5. For each model instructions and comments are includedspecific. Graphic associated with each model are included inanother worksheet to which could be accessed by clicking on the tabGRAPHICS call and located at the bottom of the spreadsheet.FINC-Chapter 4Problem related to the worksheet: the value of money over timeUsing the computer model in C04.xlsx file spreadsheet to solve this problem.a. Establish a repayment plan for a loan of $ 30,000 to be paid in equal installments at the end of each of the next 20 years at an interest rate of 10 percent. What is the annual payment?b. Establish a plan for repayment of a loan of $ 60,000 to be paid in 20 equal annual installments at an interest rate of 10 percent. What is the annual payment?c. Establish a plan for repayment of a loan of $ 60,000 to be paid in 20 equal annual installments at an interest rate of 20 percent. What is the annual payment?Chapter 6 Worksheet on the problem (C06)Valuation of a bonus1. There are a number of instructions which should be familiar to use this computer model, which appear in a separate worksheet named Instructions. If you still have not read, it is necessary to do so. To do this, click on the tab of this worksheet named Instructions.2. The chart shows the composition of total return, r d, can be displayed by clicking the worksheet named graph is located at the bottom of this worksheet. To return to this page, click on the C06 tab on the bottom of the work sheet GRAPHICS.3. The model is set up so that you can resolve cases of bonds of up to 20 years maturity.PARTIES a – cDATA ENTRY: KEY FINDINGS:Remaining years to maturity 14 Current price (P 0) $ 1,000.00Coupon interest rate 10% Rend. Capital gains this year 0.00%Interest payments per year 1 Rend. Current year 10:00%Market rate (rend. At maturity), rd 10% Rend. Total this year 10:00%Maturity value $ 1,000Data generated by the model:Years to maturity Value at beginning of year ($) Value at end of year ($) Capital gains ($) Rend.Ganancias capital (%) Interest ($) Rend.actual (%) Rend.Total (%)twenty19181716fifteen14 $ 1,000.00 $ 1,000.00 $ 0.00 0.00% $ 100 10:00% 10:00%13 $ 1,000.00 $ 1,000.00 $ 0.00 0.00% $ 100 10:00% 10:00%12 $ 1,000.00 $ 1,000.00 $ 0.00 0.00% $ 100 10:00% 10:00%eleven $ 1,000.00 $ 1,000.00 $ 0.00 0.00% $ 100 10:00% 10:00%10 $ 1,000.00 $ 1,000.00 $ 0.00 0.00% $ 100 10:00% 10:00%9 $ 1,000.00 $ 1,000.00 $ 0.00 0.00% $ 100 10:00% 10:00%8 $ 1,000.00 $ 1,000.00 $ 0.00 0.00% $ 100 10:00% 10:00%7 $ 1,000.00 $ 1,000.00 $ 0.00 0.00% $ 100 10:00% 10:00%6 $ 1,000.00 $ 1,000.00 $ 0.00 0.00% $ 100 10:00% 10:00%5 $ 1,000.00 $ 1,000.00 $ 0.00 0.00% $ 100 10:00% 10:00%4 $ 1,000.00 $ 1,000.00 $ 0.00 0.00% $ 100 10:00% 10:00%3 $ 1,000.00 $ 1,000.00 $ 0.00 0.00% $ 100 10:00% 10:00%2 $ 1,000.00 $ 1,000.00 $ 0.00 0.00% $ 100 10:00% 10:00%1 $ 1,000.00 $ 1,000.00 $ 0.00 0.00% $ 100 10:00% 10:00%0 $ 1,000.00Part DDATA ENTRY: KEY FINDINGS:Original maturity (years) fifteen Performance for the new investor (buyer):Coupon interest rate 9% Rend. at maturity, r d 9 o’clock%Interest payments per year 2 Market price one year after sale $ 1,000.00Maturity value $ 1,000 Capital gains money for next year $ 0.00Market rate (rend. At maturity) 10:00% Rend. profit / capital for next year 0.00%Purchase price $ 923.14 Current yield for next year 9 o’clock%Sale price $ 1,000.00 Total return for next year 9 o’clock%Number of years of ownership 3Returns to the original investor (Assume the market rate, the yield to maturity remains the same every year has the bonus, as when you bought it, except at the end of the year.)# Years possession bonus Value at beginning of year ($) Value at end of year ($) Capital gains ($) Rend.Ganancias capital (%) Interest ($) Rend.actual (%) Rend. Total (%)1 $ 923.14 $ 925.51 $ 2.37 0.26% $ 90 9.75% 10.01%2 $ 925.51 $ 928.12 $ 2.61 0.28% $ 90 9.72% 10.01%3 $ 928.12 $ 1,000.00 $ 71.88 7.74% $ 90 9.70% 17.44%45GENERAL INFORMATION TO SOLVE PROBLEMSCOMPUTERWe have previously entered data in this file to the base caseeach model, and these are designed for the analysis of preceding partsof the problem. You should only enter the details of each of the partiesthe remaining problem-we point out in each problem, the partiesto be performed using the spreadsheet. However, there are severalthings to consider before entering the model:1. The input data are introduced into the cells specified in sectionDATA ENTRY. When an input is changed, the modelautomatically recalculates the values of the key results, unlessto the contrary. If the values do not change automatically, press theF9 to recalculate values.2. The key results are displayed on the right sectionDATA ENTRY or immediately below it. This distribution allowsmodify an input and immediately see how the change affects the resultskey model. This is quite useful in a sensitivity analysis.3. The input data can be modified differ from those who should notmodify. Those who can mdificar are highlighted in color (blue) toUnlike other data are black.4. All percentages must be entered as decimal. The pesos, dollarsand other numbers should be entered without signs of pesos or dollars without commas.5. instructions and comments for each model are included.Graphic associated with each model are included on a separate sheetwork which can be accessed by clicking on the tabcall graph is located at the bottom of the spreadsheet.FINC4-Chapter 6Issue with the spreadsheet: Rating bondsYou need to use sheet file C06.xlsx calculation to solve this problem.Jenna bought a bond that was issued by industries Sherlock Watson (SWI) three years ago. The bond has a maturity value of $ 1,000 coupon rate equal to 9 percent and matures in 17 years. The interest is paid every six months; the next interest payment will be in six months from today.a. If the return on investment of similar risk is 11 percent, what is the current market value (price) of the bond?b. Calculate the capital gains yield, current yield and total return that Jenna will win if you hold the bond until it matures. Assume that the market rate will not change in the future.c. Suppose Jenna decides he wants to sell the bond in seven years from now, when there are 10 years to maturity. If the market rate is 8 percent in seven years, at what price will sell the bond Jenna? Calculate the capital gains yield, current yield and total return that the new investor will earn if he or she keeps the bond until it matures after 10 years. Explain why the performance is negative capital gains each year until maturity. Assume the market rate does not change from the time Jenna sell the bond until it maturesd. Suppose Jenna just bought a newly issued 15 years with a coupon rate equal to 7 percent bonus. If Jenna sell the bond at the end of the year when the market price is $ 917, (i) what would be the yield to maturity (YTM) and (ii) what performance she win? What portion of total return represents capital gains and what part represents the actual performance? (Iii) what performance would win the new investor in the year after James sell the bond? Assume interest is paid semiannually.e. Suppose James just bought the same bond of 15 who bought Jenna while. If James sells his bonus in five years from the day you bought it (with 10 years remaining to maturity) for $ 1,074 (i) what would bond yield to maturity when he sells it and (ii) what performance win for the time held the bond? What portion of the capital gains yield represents and what part represents the actual performance? (Iii) what performance would win the new investor in the year after James sell the bond? Assume interest is paid semiannually.© 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.