# This document was exported from Numbers

## Export Summary

 This document was exported from Numbers. Each table was converted to an Excel worksheet. All other objects on each Numbers sheet were placed on separate worksheets. Please be aware that formula calculations may differ in Excel. Numbers Sheet Name Numbers Table Name Excel Worksheet Name Grading Sheet Table 1 Grading Sheet Savings and Loan Analysis Table 1 Savings and Loan Analysis Budget Cost Projection Table 1 Budget Cost Projection Conversions Table 1 Conversions

&”Helvetica Neue,Regular”&12&K000000&P

## Savings and Loan Analysis

 2 Enter your full name here (If fewer than 9 letters, add additional arbitrary letters) Madison Macpherson 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 1996 1980 1975 APR Month 8 10 1 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. Electric \$72.50 Legend Gas \$72.50 If a cell is shaded You should Water \$50.00 Blue Enter a text response Other \$0.00 Green Enter a number Total Cost \$195.00 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 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): …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 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) \$15,000.00 \$15,000.00 \$15,000.00 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 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 Payment amount (PMT): …Currency with 2 decimal places Total amount paid over the time of the loan: …Currency with 2 decimal places Total amount of interest paid: …Currency with 2 decimal places Comparison 7 Use Excel formulas to transfer your amounts from above After 5 years After 10 years After 15 years Format the entries in each row as… Total savings from energy improvements …Currency with 2 decimal places Total loan payments …Currency with 2 decimal places Have you broken even at this point (yes or no)? (“Breaking even” here means that your total savings outweigh your total loan payments.)

&”Helvetica Neue,Regular”&12&K000000&P

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

 Your name (brought forward from the Savings and Loan Analysis sheet): Madison Macpherson CPI Value Month Year Reference CPI 5 2015 Legend CPI one year later If a cell is shaded You should Yearly inflation rate (r) Blue Enter a text response Green Enter a number Gold Enter an Excel formula Any other color Make no changes Value of t Projected Budget Percent Increase over Current Budget Current Monthly Budget (B) \$1,999.00 Monthly Budget next year Monthly Budget in 5 years Monthly Budget in 10 years

&”Helvetica Neue,Regular”&12&K000000&P

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

 Your monthly savings in dollars 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 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 T M A M A 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 1060 units in the country’s currency (note that the Example uses 1000 units) \$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) Guinea Libya Paraguay Taiwan Angola Chile Guyana Macau (China) Peru Tanzania Argentina China 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

&”Helvetica Neue,Regular”&12&K000000&P

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/currencyconverterThen, 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.