How to I get the monthly savings calculation in C34? This is my assignment. What is the exact formula?

125% v . . T Collaborate So Zoom nsert Table Chart Text Shape Media Comment Savings and Loan Analysis Conversions Grading Sheet Budget Cost Projection C D E G H 1 In Major Assignment 1, you created a monthly budget, which included a recurring cost for utilities. Here, you'll consider making some energy-saving home improvements and compare your potential savings against paying off the cost of those improvements. Below, you'll start by entering and adding up the costs of your electric, gas, water, and other energy utilities. Then, given a percent savings due to your energy-saving improvements, you'll calculate how much you'll save over the next 5, 10, and 15 years if you contribute your monthly savings into an account with a given APR. Here, you'll use the following formula for your calculations: given an amount P contributed at the end of each of n periods per year for t years and earning interest at an annual percentage rate of r, the total amount A accrued after t years is given by (in Excel format): A = P*((1+r)^(n*t)-1)/(r) Next, you'll develop a cost to install energy-efficient improvements (installing energy-efficient doors and windows, adding insulation, upgrading to more efficient appliances or lights, and so on) and then calculate a monthly payment if you were to finance the installation cost by a loan of 5, 10, or 15 years. Here, you will use this formula: given a loan principal amount P and an annual interest rate of r, the payment amount PMT required to pay off the loan with n payments per year for t years (with payments made at the end of each period) is given by (in Excel format): 8 PMT = P*(r)/ (1-(1+r)^(-n*t)) 10 Important Note: For your savings and loan payment calculations, as well as for calculating the associated interest, you MUST use direct calculation formulas, and you MAY NOT use built-in Excel functions to 11 calculate these values. 12 For all the above calculations, you will look up rates in the following historical table of 30-year fixed mortgage rates, based on the years and months specified in step 6 below. 13 14 http://www.freddiemac.com/pmms/pmms30.html 15 16 18 Mortgage Rates 19 20 21 2 Enter your full name in the blue- Assignment Advisory: You must use the latest desktop version of Excel for Microsoft 365 for this assigment. 22 shaded box here (If there are fewer Lindsey Zarate (This is provided free by GCU; contact the Help Desk for more information and help installing the software.) Using than 9 letters, add additional an earlier version of Excel or a different spreadsheet program may result in missing or corrupted template [arbitrary letters) elements. Copying cells from or into this template may likewise result in corrupted data. 23 24 Savings 25 APR Year 1986 1977 3 Look up three interest rates from 1977 the historical mortgage rate table, 26 formatting them as Percentage with 2 decimal places. Make sure to enter APR Month 12 13 these as percentage values. For example, 4.03 in the table is 4.03% 27 or 0.0403. Interest Rate 9.32% 8.69% 8.94% Text Assignment Advisory: You must use the latest desktop version of Excel for Microsoft 365 for this assigment. (This is provided free by GCU; contact the Help Desk for more information and help installing the software.) Using an earlier T Insert Table Chart Text Shape Media Comment Collaborate Format rading Sheet Savings and Loan Analysis Budget Cost Projection Conversions B G H r your full name in the blue- Assignment Advisory: You must use the latest desktop version of Excel for Microsoft 365 for this assigment. box here (If there are fewer Lindsey Zarate (This is provided free by GCU; contact the Help Desk for more information and help installing the software.) Using letters, add additional an earlier version of Excel or a different spreadsheet program may result in missing or corrupted template ry letters) elements. Copying cells from or into this template may likewise result in corrupted data. Savings APR Year . 1986 1977 1977 ook up three interest rates from historical mortgage rate table, matting them as Percentage with cimal places. Make sure to enter APR Month 12 3 7 hese as percentage values. For ample, 4.03 in the table is 4.03% or 0.0403. Interest Rate 9.32% 8.69% 8.94% Electric $100.00 Legend Gas $60.00 If a cell is shaded You should Water $50.00 Blue Enter a text response + Enter, or estimate, your monthly Other $75.00 Green Enter a number utility costs, then calculate your Total Cost $285.00 Gold Enter an Excel formula total monthly cost and monthly savings. Format all cells as Currency showing the $ symbol and with 2 Monthly Percent Savings Complete the first interest rate Any other color Make no changes decimals of precision. entry in section 3 above Monthly Savings (total cost times percent savings) 5 Complete this table for your 5- Calculation #1 Calculation #2 Calculation #3 Format the entries in each year, 10-year, and 15-year savings (5-year savings) 10-year savings) 15-year savings) row as... Contribution amount (P) (Bring forward your Monthly Savings ...Currency with 2 decimal amount, using a formula, for each places entry) APR from the table (r) Complete the second interest Complete the second interest Complete the second interest rate entry in section 3 above rate entry in section 3 above rate entry in section 3 above ...a Number with 0 decimal Number of contributions per year (n) places Number of vears (t ) ...a Number with 0 decimal