1. Create a name for your savings and loan and put titles on each page to reflect the name of your organization. Include the current date and time in the title. This date and time should update to reflect the current date and time each time the workbook is opened. Include the name

of all group members in the footer of each sheet.

2. The first worksheet, named Calculator in the workbook, must be designed to accept the following data as what-if input:

Assessed value	Value of the property that is being purchased. This is the value used for property tax and insurance calculations; it is not used for mortgage loan calculations.
Mortgage amount	The overall value of the mortgage.
Down payment	The amount of money that the loan seeker has available to pay towards the mortgage.
State	The state where the loan is being made. The state is used for property tax and insurance calculations; it is not used for mortgage loan calculations.
Duration in years	The expected duration of the loan.
Payments/year	The number of separate payments that will be made each year by the loan seeker.
Interest rate/year	Interest rate (APR) for the loan.

3. The Calculator worksheet will calculate the payment required to repay the principal and interest for the loan. This is the periodic payment shown on the sample worksheet.

4. The Calculator worksheet should calculate the property tax and insurance payments. These payments are based on the data in the StateRates worksheet that you imported. The StateRates worksheetliststheabbreviationforeachstateinthefirstcolumn,andtheyearly percentagesfor propertytaxandinsurancearelistedinthefifthandsixthcolumns, respectively. To calculate the property tax and/or insurance contributions to the total payment, the rate is multiplied by the assessed value, then divided by the number of separate payments that will be made each year.

4. Total payment is the sum of the periodic payment, property tax and insurance.

5. Create an amortization schedule on the Calculator worksheet, as shown in the sample

worksheet. The amortization schedule should go to a maximum size of 30 years with 12 payments per year (360 periods). The remaining principal, interest payment, and principal payment formulas should be written so that if any of the input values change, the amounts will be automaticallyupdated.

6. To avoid #NUM! or #VALUE errors in periods past the end of the loan, nest your principal and interest payment formulas inside an IF statement to return a 0 if no further interest orprincipalpaymentsarerequired.

7. On the second worksheet named Tax, create a table listing years 1-30 and calculate:

Cumulative interest payments for each year. Write a formula that automatically calculates this value for the corresponding periods so that it can be copied down for each year. Assume that the loans all begin in January so that no partial years need to be calculated. Note that to accommodate variable periods (the payments/yearare variableandcouldrepresentmonths,quarters,etc.)the beginningandendingperiods mustbecalculationsthatreferencethenumberof periodsperyearontheCalculator worksheet.

Expected tax deduction for three different tax rates. In three adjacent columns, calculate the value of the expected tax deduction for tax rates of 15%, 28%, and 32% forthecorrespondingyear(cumulativeinterestpayments*taxrate). Your formula fortaxdeductionshoulddragbothdownthecolumnandacrosstherow.

Total amount of interest paid. Sum the cumulative interest.

State Savings and Loan Rates by State List

Abbreviation Name Region Region# Property Tax Rate Home Insurance Rate

AL ALABAMA SE 2 0.31% 0.32%

AK ALASKA W 5 1.14% 0.81%

AZ ARIZONA SW 4 0.61% 0.65%

AR ARKANSAS SE 2 0.53% 0.56%

CA CALIFORNIA W 5 0.48% 0.54%

CO COLORADO W 5 0.58% 0.59%

CT CONNECTICUT NE 1 1.42% 0.75%

DE DELAWARE NE 1 0.40% 0.12%

DC DISTRICT OF COLUMBIA NE 1 0.38% 0.59%

FL FLORIDA SE 2 0.79% 1.20%

GA GEORGIA SE 2 0.71% 0.56%

HI HAWAII W 5 0.20% 1.08%

ID IDAHO W 5 0.91% 0.52%

IL ILLINOIS MW 3 1.58% 0.85%

IN INDIANA MW 3 0.94% 0.02%

IA IOWA MW 3 1.27% 0.33%

KS KANSAS MW 3 1.24% 0.58%

KY KENTUCKY SE 2 0.67% 0.51%

LA LOUISIANA SE 2 0.17% 0.95%

ME MAINE NE 1 1.12% 0.12%

MD MARYLAND NE 1 0.77% 0.45%

MA MASSACHUSETTS NE 1 0.82% 0.95%

MI MICHIGAN MW 3 1.24% 0.41%

MN MINNESOTA MW 3 0.81% 0.53%

MS MISSISSIPPI SE 2 0.50% 0.71%

MO MISSOURI MW 3 0.82% 0.07%

MT MONTANA W 5 0.99% 0.09%

NE NEBRASKA MW 3 1.67% 1.20%

NV NEVADA W 5 0.51% 0.68%

NH NEW HAMPSHIRE NE 1 1.63% 0.91%

NJ NEW JERSEY NE 1 1.60% 0.85%

NM NEW MEXICO SW 4 0.56% 0.41%

NY NEW YORK NE 1 2.19% 1.76%

NC NORTH CAROLINA SE 2 0.76% 0.45%

ND NORTH DAKOTA MW 3 1.50% 0.65%

OH OHIO MW 3 1.23% 0.12%

OK OKLAHOMA SW 4 0.71% 0.31%

OR OREGON W 5 0.95% 0.48%

PA PENNSYLVANIA NE 1 1.47% 0.87%

RI RHODE ISLAND NE 1 1.09% 0.82%

SC SOUTH CAROLINA SE 2 0.57% 0.50%

SD SOUTH DAKOTA MW 3 1.38% 0.45%

TN TENNESSEE SE 2 0.70% 0.12%

TX TEXAS SW 4 1.82% 1.27%

UT UTAH W 5 0.68% 0.25%

VT VERMONT NE 1 1.63% 0.75%

VA VIRGINIA SE 2 0.67% 0.12%

WA WASHINGTON W 5 0.99% 0.32%

WV WEST VIRGINIA SE 2 0.46% 0.15%

WI WISCONSIN MW 3 1.82% 0.65%

WY WYOMING W 5 0.55% 0.43%