Unsure how to do the formulas for this sheet. Data is included in a picture and
ID: 2577747 • Letter: U
Question
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%
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.
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+Explanation / Answer
S.No Property tax rate Home insuranced rate AL ALABAMA SE 2 0.31% 0.32% 0.31 0.32 AK ALASKA W 5 1.14% 0.81% 1.14 0.81 AZ ARIZONA SW 4 0.61% 0.65% 0.61 0.65 AR ARKANSAS SE 2 0.53% 0.56% 0.53 0.56 CA CALIFORNIA W 5 0.48% 0.54% 0.48 0.54 CO COLORADO W 5 0.58% 0.59% 0.58 0.59 CT CONNECTICUT NE 1 1.42% 0.75% 1.42 0.75 DE DELAWARE NE 1 0.40% 0.12% 0.4 0.12 DC DISTRICT OF COLUMBIA NE 1 0.38% 0.59% 0.38 0.59 FL FLORIDA SE 2 0.79% 1.20% 0.79 1.2 GA GEORGIA SE 2 0.71% 0.56% 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%
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.