All tabs need to be completed and and need to be in a spread sheet
Grading Sheet Major Assignment 2 Grading Sheet CompetencyRequirements for full credit(optional for student use) Did you meet the requirements?Points possibleYour pointsScoring comments Savings and Loan AnalysisNameYou have entered your full name in the field provided.1 Interests RatesThe interest rates you have entered come from the mortgage rates table and match those for the months and years provided.3 You have explicitly formatted the cells to display as Percentage with 2 decimal places of precision.3 Monthly Costs and SavingsYour Electric, Gas, Water, and Other entries are reasonable values, with at least two nonzero entries. For zero entries, you have explicitly entered values of 0.4 Your Total Cost and Monthly Savings formulas are correct and use appropriate cell references.8 All cost cells are formatted as Currency showing the $ symbol and with 2 decimal places of precision.6 Savings TableYou have brought forward your monthly savings amount, using Excel formulas.3 Your number of contributions per year and number of years entries are correct.6 Your formulas for total amount saved, total contributions, and total accrued interest are correct and use cell references.36 All cells are explicitly formatted with the format given in the last column of the table.18 Loan TableYou have entered the correct number of contributions per year and number of years.6 Your formulas for payment amount, total amount paid, and total amount of interest paid are correct and use cell references as inputs.36 All cells are explicitly formatted with the format given in the last column of the table.15 ComparisonYou have correctly brought forward your savings and loan amounts, using cell references.6 Your savings and loan cells are explicitly formatted with the format given in the last column of the table.6 You have answered the comparison questions correctly, answering either "yes" or "no" for each one.3 Subtotals1600 Budget Cost ProjectionInflation Rate CalculationYour reference CPI is correct for the month and year given.1 Your next-year CPI, month, and year are correct.3 Your inflation rate calculation is correct.6 Your CPI values and inflation rate are explicitly formatted as indicated in the instructions.3 Budget ProjectionsYou have correctly entered your Budget Total from cell G21 of the Monthly Budget sheet from your Major Assignment 1.2 Your "value of t" entries are correct.3 Your 1-year, 5-year, and 10-year projections are correct Excel formulas using cell references.12 Your percent increase calculations are correct Excel formulas using cell references.12 Your Current Budget, Projected Budget and Percent Increase cells are formatted as indicated in the instructions.7 Subtotals490 ConversionsMonthly SavingsYou have brought forward your monthly savings amount from the Savings and Loan Analysis sheet, using an Excel formula with a sheet and cell reference.2 Currency ConversionsYou have entered the first two letters of your first and last names, using the letter M if one or both names consist of only one letter.4 You have chosen appropriate countries from the list provided below the table, using the procedure described in the instructions.4 You have entered the date(s) on which you looked up the exchange rates for your currencies, and all dates are within 2 weeks of the due date of your assignment.4 You have entered both the full name of your country's currency and the correct currency code as indicated on the website.8 You have provided each exchange rate to at least 5 significant digits, and the exchange rate matches the rate for the date you looked it up.4 Your savings amount in the foreign currency is a correct Excel formula, using cell references.16 Your calculation of the value of foreign currency units into dollars are correct Excel formulas, using cell references. (Note that the amount to convert is autogenerated and may differ from the amount shown in assignment resources.)16 The cells containing your dates, savings amounts, and value of foreign currency converted to dollars are correctly formatted as specified in the last column of the table.12 Subtotals700 Totals2790 Percentage100.00%0.00% Scaled out of 100100.000.00 Savings and Loan Analysis 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)^(n*t)-1)/(r/n) 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): PMT = P*(r/n)/(1-(1+r/n)^(-n*t)) 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. 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. http://www.freddiemac.com/pmms/pmms30.html 2 Enter your full name in the blue-shaded box here (If there are fewer than 9 letters, add additional arbitrary letters)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 version of Excel or a different spreadsheet program may result in missing or corrupted template elements. Copying cells from or into this template may likewise result in corrupted data. Savings 3 Look up three interest rates from the historical mortgage rate table, formatting them as Percentage with 2 decimal places. Make sure to enter these as percentage values. For example, 4.03 in the table is 4.03% or 0.0403. APR Year Your full name entry must be longerYour full name entry must be longerYour full name entry must be longer APR Month Your full name entry must be longerYour full name entry must be longerYour full name entry must be longer Interest Rate 4 Enter, or estimate, your monthly utility costs, then calculate your total monthly cost and monthly savings. Format all cells as Currency showing the $ symbol and with 2 decimals of precision.ElectricLegend GasIf a cell is shadedYou should WaterBlueEnter a text response OtherGreenEnter a number Total CostGoldEnter an Excel formula Monthly Percent Savings Complete the first interest rate entry in section 3 aboveAny other colorMake no changes Monthly Savings (total cost times percent savings) 5 Complete this table for your 5-year, 10-year, and 15-year savingsCalculation #1 (5-year savings)Calculation #2 (10-year savings)Calculation #3 (15-year savings)Format the entries in each row as… Contribution amount (P) (Bring forward your Monthly Savings amount, using a formula, for each entry)…Currency with 2 decimal places APR from the table (r) Complete the second interest rate entry in section 3 aboveComplete the second interest rate entry in section 3 aboveComplete the second interest rate entry in section 3 above Number of contributions per year (n)…a Number with 0 decimal places Number of years (t)…a Number with 0 decimal places Total amount saved (A):…Currency with 2 decimal places Total contributions:…Currency with 2 decimal places Total accrued interest:…Currency with 2 decimal places Loan 6 Continue by completing this table for your 5-year, 10-year, and 15-year loans, based on the principal and interest rates given with monthly paymentsCalculation #1 (5-year loan)Calculation #2 (10-year loan)Calculation #3 (15-year loan)Format the entries in each row as… Loan principal (P) Your full name entry must be longerYour full name entry must be longerYour full name entry must be longer APR from the table (r), with a slightly higher rate for longer loansComplete the third interest rate entry in section 3 aboveComplete the third interest rate