# All other objects on each Numbers sheet were placed on separate worksheets. Please be aware that formula calculations may differ in Excel.

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

&”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