Please read before accepting the assignment

Answer directly on the worksheet. 

This assignment uses a scoring guide. Review the scoring guide on the first tab of the spreadsheet prior to beginning the assignment to become familiar with the expectations for successful completion.

2

>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 T

he 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
Month

ly 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 correct and use appropriate cell references.

4
All cost cells are formatted as Currency showing the $ symbol and with 2 decimal places of precision. 6 Savings Table You 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. 1

8 All cells are explicitly formatted with the format given in the last column of the table.

18
Loan Table You 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.

18
All cells are explicitly formatted with the format given in the last column of the table.

15 Comparison You 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
Subtotals 12

0

0

Budget Cost Projection

Inflation Rate Calculation Your 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.

3
Your CPI values and inflation rate are explicitly formatted as indicated in the instructions.

3
Budget Projections You 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.

6
Your percent increase calculations are correct Excel formulas using cell references.

6
Your Current Budget,

Projected Budget

and Percent Increase cells are formatted as indicated in the instructions. 7 Subtotals

34

0

Conversions

Monthly Savings

You 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 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 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.

8
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.)

8
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
Subtotals

54

0
Totals 208

0
Percentage

10

0.00

%

0.00%
Scaled out of 100 100.00

0.00

Savings and Loan Analysis

Savings

Your full name entry must be longer Your full name entry must be longer

Your full name entry must be longer Your full name entry must be longer Your full name entry must be longer

Electric

Gas

Water

Other

Total Cost

Complete the second interest rate entry in section 3 above Complete the second interest rate entry in section 3 above

…a Number with 0 decimal places

…Currency with 2 decimal places

…Currency with 2 decimal places

…Currency with 2 decimal places

Loan

Format the entries in each row as…

Your full name entry must be longer Your full name entry must be longer Your full name entry must be longer

Complete the third interest rate entry in section 3 above Complete the third 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

…Currency with 2 decimal places

…Currency with 2 decimal places

…Currency with 2 decimal places

Comparison

Format the entries in each row as…

…Currency with 2 decimal places

…Currency with 2 decimal places

2 Enter your full name here (If 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.

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 longer

APR Month

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.

Legend

If a cell is shaded

You should

Blue

Enter a text response

Green

Enter a number

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)

…Currency with 2 decimal places

APR from the table (r)

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)

Total amount saved (A):

Total contributions:

Total accrued interest:

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)

Loan principal (P)

APR from the table (r), with a slightly higher rate for longer loans

Complete the third interest rate entry in section 3 above

Payment amount (PMT):

Total amount paid over the time of the loan:

Total amount of interest paid:

7 Use Excel formulas to transfer your amounts from above

After 5 years

After 10 years

After 15 years

Total savings from energy improvements

Total loan payments

Have you broken even at this point (yes or no)? (“Breaking even” here means that your total savings outweigh your total loan payments.)

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))
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

(Mortgage Rates)

http://www.freddiemac.com/pmms/pmms30.html

Budget Cost Projection

0

Month Year

Your full name entry must be longer Your full name entry must be longer Legend

If a cell is shaded You should
Blue Enter a text response
Green Enter a number
Gold Enter an Excel formula
Any other color Make no changes

Projected Budget

Your name (brought forward from the Savings and Loan Analysis sheet):
CPI Value
Reference CPI
CPI one year later
Yearly inflation rate (r)
Value of t	Percent Increase over Current Budget
Current Monthly Budget (B)
Monthly Budget next year
Monthly Budget in 5 years
Monthly Budget in 10 years

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:
1. Go to Bureau of Labor Statistics page link

https://data.bls.gov/cgi-bin/surveymost?cu

(or use link below)
2. Check the box to the left of text “U.S. city average, All items – CUUR0000SA0”
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.
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.

9 Next, enter your budget total from cell G21 of the Monthly Budget sheet from your Major Assignment 1. Then, use the following formula to project your monthly budget forward 1, 5, and 10 years into the future:
A = B*(1+r)^t
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 budget is in percent than your initial budget. Format your Projected Budget entries as Currency with the $ symbol and 2 decimals of precision; format your Percent Increase cells as Percentages with 2 decimals of precision.

(CPI Values)

https://data.bls.gov/cgi-bin/surveymost?cu

Conversions

Legend

If a cell is shaded You should
Blue Enter a text response
Green Enter a number
Gold Enter an Excel formula
Any other color Make no changes

T

Your full name entry must be longer

Guinea

)

China

Your monthly savings in dollars
Example	First letter of your first name	Second letter of your first name	First letter of your last name	Second letter of your last name	Format this entry as
The letter
Country starting with the letter (or next available letter)	Tajikistan
The date that you looked up the conversion rate (must be within 2 weeks of your assignment due date)	5/23/20	Date
Full name of the country’s currency as listed on the XE website	Tajikistani somoni
Currency code (ISO-4217)	TJS
Exchange rate for the currency to at least 5 significant digits (or exact rate if there are fewer than 5 significant digits)	10.2686311694
Your savings in the country’s currency. Note that you must enter a formula here and then format the cell to display the currency code; do not enter text in this cell.	[$TJS] 0.00	Currency with the country’s currency code as a symbol
$97.38	Currency with the $ symbol
Choose your countries from this list
Afghanistan	Cambodia	Guatemala	Lebanon	Pakistan	Switzerland
Albania	Canada	Guernsey (UK)	Liberia	Papua New	Guinea	Syria
Algeria	Cayman Islands (UK)	Libya	Paraguay	Taiwan
Angola	Chile	Guyana	Macau (	China	Peru	Tanzania
Argentina	Haiti	Madagascar	Philippines	Thailand
Armenia	Colombia	Honduras	Malawi	Poland	Tonga
Aruba (Netherlands)	Comoros	Hong Kong (China)	Malaysia	Qatar	Trinidad and Tobago
Australia	Congo, Democratic Republic of the	Hungary	Maldives	Romania	Tunisia
Azerbaijan	Costa Rica	Iceland	Mauritania	Russia	Turkey
Bahamas	Croatia	India	Mauritius	Rwanda	Turkmenistan
Bahrain	Cuba	Indonesia	Mexico	Saint Helena (UK)	Uganda
Bangladesh	Czechia	International Monetary Fund (IMF)	Moldova	Samoa	Ukraine
Barbados	Denmark	Iran	Mongolia	Sao Tome and Principe	United Arab Emirates
Belarus	Djibouti	Iraq	Morocco	Saudi Arabia	United Kingdom
Belize	Dominica	Isle of Man (UK)	Mozambique	Serbia	Uruguay
Bermuda (UK)	Dominican Republic	Israel	Myanmar (formerly Burma)	Seychelles	Uzbekistan
Bhutan	Egypt	Jamaica	Namibia	Sierra Leone	Vanuatu
Bolivia	Eritrea	Japan	Nepal	Singapore	Venezuela
Bosnia and Herzegovina	Ethiopia	Jersey (UK)	New Zealand	Somalia	Vietnam
Botswana	Falkland Islands (UK)	Jordan	Nicaragua	South Africa	Wallis and Futuna (France)
Brazil	Fiji	Kazakhstan	Nigeria	South Korea	Yemen
Brunei	Gambia	Kenya	North Korea	Sri Lanka	Zambia
Bulgaria	Georgia	Kuwait	North Macedonia (formerly Macedonia)	Sudan
Burundi	Ghana	Kyrgyzstan	Norway	Suriname
Cabo Verde	Gibraltar (UK)	Laos	Oman	Sweden

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 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:

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 following web page:

https://www.xe.com/currencyconverter

11 Now, from the list below the table below, select four countries that start with the first two letters of your 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 name that is one letter long. If there is no country starting with a particular letter or you have run out of 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.)

(Currency Converter)

https://www.xe.com/currencyconverter

Then, convert your monthly savings above into this currency and a given number of units of the local currency into dollars. These calculations must be Excel formulas that use a cell reference for the exchange rate; you may not use the currency converter link for this calculation (although you’re welcome to check your calculation there).
Add special formatting as indicated in the last column of the table. Other entries may use general formatting.
An example is provided for you. Note that this country is not available for you to choose from the list.