Unsure how to do the formulas for this sheet. Data is included in a picture and copy and pasted below. Formatting for the Calculator sheet is also included in a picture.

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.

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.

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.

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

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.

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.

\

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%

State S and Loan Mo Calculator 3 Assessed Value Period Payment Property Tax Insurance Total Payment Mortgage Amount Down Payment 6 State 7 Duration in Years 9 Intrest Rate/Year 11 Period NumberRemaining Principal Intrest Payment Principal Payment 17 21 Calculator Tax State Rates+