Grading Sheet
Major Assignment 2 Grading Sheet
Competency Requirements for full credit (optional for student use) Did you meet the requirements? Points possible Your points Scoring comments
Savings and Loan Analysis Name You have entered your full name in the field provided. 1
Interests Rates The 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 Savings Your 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 co
ect and use appropriate cell references. 8
All cost cells are formatted as Cu
ency showing the $ symbol and with 2 decimal places of precision. 6
Savings Table You have
ought forward your monthly savings amount, using Excel formulas. 3
Your number of contributions per year and number of years entries are co
ect. 6
Your formulas for total amount saved, total contributions, and total accrued interest are co
ect and use cell references. 36
All cells are explicitly formatted with the format given in the last column of the table. 18
Loan Table You have entered the co
ect 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 co
ect and use cell references as inputs. 36
All cells are explicitly formatted with the format given in the last column of the table. 15
Comparison You have co
ectly
ought 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 co
ectly, answering either "yes" or "no" for each one. 3
Subtotals 160 0
Budget Cost Projection Inflation Rate Calculation Your reference CPI is co
ect for the month and year given. 1
Your next-year CPI, month, and year are co
ect. 3
Your inflation rate calculation is co
ect. 6
Your CPI values and inflation rate are explicitly formatted as indicated in the instructions. 3
Budget Projections You have co
ectly entered your Budget Total from cell G21 of the Monthly Budget sheet from your Major Assignment 1. 2
Your "value of t" entries are co
ect. 3
Your 1-year, 5-year, and 10-year projections are co
ect Excel formulas using cell references. 12
Your percent increase calculations are co
ect Excel formulas using cell references. 12
Your Cu
ent Budget, Projected Budget and Percent Increase cells are formatted as indicated in the instructions. 7
Subtotals 49 0
Conversions Monthly Savings You have
ought forward your monthly savings amount from the Savings and Loan Analysis sheet, using an Excel formula with a sheet and cell reference. 2
Cu
ency Conversions You 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 cu
encies, 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 cu
ency and the co
ect cu
ency 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 cu
ency is a co
ect Excel formula, using cell references. 16
Your calculation of the value of foreign cu
ency units into dollars are co
ect 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 cu
ency converted to dollars are co
ectly formatted as specified in the last column of the table. 12
Subtotals 70 0
Totals 279 0
Percentage 100.00% 0.00%
Scaled out of 100 100.00 0.00
Savings and Loan Analysis
1 In Major Assignment 1, you created a monthly budget, which included a recu
ing 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):
XXXXXXXXXXA = P*((1+
n)^(n*t)-1)/(
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):
XXXXXXXXXXPMT = P*(
n)/(1-(1+
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.
XXXXXXXXXXhttp:
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 a
itrary 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 co
upted template elements. Copying cells from or into this template may likewise result in co
upted 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 Yea
Your full name entry must be longer Your full name entry must be longer Your full name entry must be longe
APR Month
Your full name entry must be longer Your full name entry must be longer Your full name entry must be longe
Interest Rate
4 Enter, or estimate, your monthly utility costs, then calculate your total monthly cost and monthly savings. Format all cells as Cu
ency showing the $ symbol and with 2 decimals of precision. Electric Legend
Gas If a cell is shaded You should
Water Blue Enter a text response
Other Green Enter a numbe
Total Cost Gold Enter an Excel formula
Monthly Percent Savings
Complete the first interest rate entry in section 3 above Any other color Make no changes
Monthly Savings (total cost times percent savings)
5 Complete this table for your 5-year, 10-year, and 15-year savings Calculation #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) …Cu
ency with 2 decimal places
APR from the table (r)
Complete the second interest rate entry in section 3 above Complete the second interest rate entry in section 3 above Complete 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): …Cu
ency with 2 decimal places
Total contributions: …Cu
ency with 2 decimal places
Total accrued interest: …Cu
ency 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 payments Calculation #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 longer Your full name entry must be longer Your full name entry must be longe
APR from the table (r), with a slightly higher rate for longer loans Complete the third interest rate entry in section 3 above Complete the third interest rate