B 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) 8 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 9 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 10 payments per year for t years (with payments made at the end of each period) is given by (in Excel format): 11 12 PMT = P*(r)/(1-(1+r)^(-n*t)) 15 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 calculate these values. 16 7 For all the above calculations, you will look up rates in the "Historical Mortgage Rates" table of 30-year fixed mortgage rates, based on the years and months specified in step 6 below. (This table is included as a tab in this template.) 18 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. (This is shaded box here (If there are fewer than Emily Thurman provided free by GCU; contact the Help Desk for more information and help installing the software.) Using an earlier version of Excel or a different spreadsheet program may result in missing or corrupted template elements. Copying cells from or into this 9 letters, add additional arbitrary letters) 22 template may likewise result in corrupted data. 23 24 Savings 3 Look up three interest rates from the APR Year 1977 1978 1979 25 historical mortgage rate table, formatting them as Percentage with 2 decimal 5 places. Make sure to format these as APR Month 12 26 percentage values. For example, if 4.03 appears in the table, then it should appear here as 4.03% Interest Rate 8.94% 9.57% 12.90% 27 28 29 Electric Legend 30 Gas f a cell is shaded You should 31 4 Enter, or estimate, your monthly Water Blue Enter a text response 32 Other Green Enter a number 33 utility costs, then calculate your total Total Cost Gold monthly cost and monthly savings. Enter an Excel formula Format all cells as Currency showing the $ symbol and with 2 decimals of Monthly Percent Savings 48.9% Any other color Make no changes 34 precision. Monthly Savings (total cost times 35 percent savings Savings and Loan Analysis Sheet1 Budget Cost Projection & Conversions Historical Mortgage Rates +B D E G H Format all cells as Currency showing the $ symbol and with 2 decimals of Monthly Percent Savings 48.9% Any other color Make no changes 34 precision Monthly Savings (total cost times 35 percent savings) 36 5 Complete this table for your 5-year, Calculation #1 Calculation #2 Calculation #3 Format the entries in each row 37 10-year, and 15-year savings 5-year savings) (10-year savings) (15-year savings) as... Contribution amount (P) (Bring forward your Monthly Savings ..Currency with 2 decimal amount from cell C35, using a cell places 38 referrence, for each entry) APR from the table (r) 9.6% 9.6% 9.6% 39 contributions per year (n) ...a Number with 0 decimal 40 places Number of years (t) ..a Number with 0 decimal 41 place Currency with 2 decimal Total amount saved (A): 42 place Total contribution over the number of ..Currency with 2 decimal 43 years: places Total accrued interest: ..Currency with 2 decimal 44 places 45 46 47 Loan 6 Continue by completing this table for your 5-year, 10-year, and 15-year loans, Calculation #1 Calculation #2 Calculation #3 Format the entries in each row based on the principal and interest rates (5-year loan) (10-year loan) (15-year loan) as... 48 given with monthly payments Loan principal (P) $20,000.00 $20,000.00 $20,000.00 APR from the table (r), with a slightly 12.9% 14.9% 16.9% 50 higher rate for longer loans Number of contributions per year (n) ..a Number with 0 decimal 51 place Jumber of years (t) ..a Number with 0 decimal 52 places Payment amount (PMT): .Currency with 2 decimal 53 place Total amount paid over the time of the ..Currency with 2 decimal 54 loan: lace Total amount of interest paid: ..Currency with 2 decimal 55 places 56 5756 57 58 Format the entries in each row 7 USE Excel formulas to transfer your After 5 years After 10 years After 15 years 59 amounts from above ...Currency with 2 decimal Total savings from en ergy improvements 60 places ...Currency with 2 decimal Total loan payments 6 1 places Have you broken even at this point (yes or no)? ("Breaking even " here means that your total savings outweigh your 52 total loan payments] 63 64 65 66 67 68 69 70 71 A B C D E F G H H 10 On this second conversion sheet, you will convert your monthly savings into the equivalent amounts in several foreign currencies and convert a given amount of the local IN currency into the equivalent number of US dollars. Start by transferring your monthly savings from the Savings and Loan Analysis sheet, using an Excel formula that references the appropriate cell directly: V Your monthly savings in dollars Legend 8 If a cell is shaded You should 1 1 Now, from the list below the table below, select four countries that start with the first two letters of your Enter a text Blue 9 response first and last names. If your first or last name is only one letter long, use the letter M as the second letter of each 10 name that is one letter long. If there is no country starting with a particular letter or you have run out of Green Enter a number countries to choose from for a particular letter, go to the next letter of the alphabet that you still have available choices for and select a country starting with that letter. (If you are at the letter Z, go back to A.) Gold Enter an Excel 11 formula 12 Any other color Make no changes 13 14 15 For each country, identify the name of the country's currency, the currency code (based on the ISO-4217 standard), and the exchange rate for $1, using the 16 following web page: https://www.xe.com/currencyconverter Currency Converter 17 18\fA B D E F G H 8 On the Monthly Budget sheet in Major Assignment 1, you evaluated your current expenses. Here, you will project your budget costs forward, using an inflation rate that you develop from values in the Consumer Price Index. As a first step, look up the CPI value for the given month and year as well as the CPI value one year later; then, calculate a yearly inflation rate based on those CPI values. Use this procedure to look up the CPI value: 00 1. Go to Bureau of Labor Statistics page link https://data.bls.gov/cgi-bin/surveymost?cu (or use link below) CPI Values 11 2. Check the box to the left of text "U.S. city average, All items - CUUROOOOSAO" 12 3. Press the "Retrieve Data" button at the bottom of the list. This should take you to a CPI table for about the last 10 years. 13 14 Here, format your CPI entries as Number with 3 decimals of precision, and format your yearly inflation rate as a Percentage with 2 decimals of precision. 15 16 17 CPI Value Month Year Reference CPI 2016 Legend 18 19 CPI one year later If a cell is shaded You should 20 Yearly inflation rate (r) Blue Enter a text response 21 Green Enter a numbe 9 Next, enter your budget total from cell G21 of the Monthly Budget sheet from your Major Assignment 1. Then, use the following Gold Enter an Excel formul 22 formula to project your monthly budget forward 1, 5, and 10 years into the future: 23 Any other color Make no changes 24 A = B*(1+r)^t 25 26 where A is the budget after t years; B is the initial budget; and r is the yearly inflation rate. Here, also calculate how much larger each 27 budget is in percent than your initial budget. Format your Projected Budget entries as Currency with the $ symbol and 2 decimals of 28 precision; format your Percent Increase cells as Percentages with 2 decimals of precision. 29 30 31 Percent Increase over Value oft Projected Budget 32 Current Budget 33 Current Monthly Budget (B) 34 Monthly Budget next year 35 Monthly Budget in 5 years 36 Monthly Budget in 10 years 37 38 39