Reference no: EM131540143
Assignment
I. Gasoline Tax Problem: (purple tab)
a. Using the GasTax named range and VLookUp, fill in the high and low gallons sold estiamtes, and the current gasoline tax, for the selected counties.
b. Using that information, and the proposed gasoline tax increase, compute the new estimate tax revenues for gasoline.
II. Cost of a House (red tab)
a. Determine the amount you must borrow to purchase the house
b. Determine the monthly payment based on the information given
c. Determine the number of payments you would make if you paid $25 more per month than the calculated payment
d. Following the instructions, determine the amount of interest you'll pay in the first year of the mortgage.
III. Shipping Costs (gold tab) 20 points
1) Using the name manager, create a named range called "ShipCharge" from the table above.
2) Using a function with a combination of a Nested IF and a Vlookup, determine the correct shipping charge for the order.
3) Determine the total price of the order.
IV. Instructions:
1) Calculate the amount you need to borrow.
2) Calculate the loan payment using the information provided. (Read carefully!)
3) How many payments would you make if you paid $25 per month more than required?
4) Fill in the table to the left. Use the following information as necessary:
4a) The information in the range d13:d24 is the number of the payment in the sequence.
4b) The information in the rage e13:e24 is that amount of the payment each month.
4c) Calculate the principle amount, the amount the loan balance is reduced each month, using the PPMT function.
4d) Calculate the interest amount, the amount of the payment that goes to interest each month, using the IPMT function.
4e) Confirm you have done this correctly by ensuring the amount of interest plus principle is equal to the amount you paid that month.
5) Provide the total amount of interest paid during this first year of the mortgage.
V.
1) Using the name manager, create a named range called "ShipCharge" from the table above.
2) Using a function with a combination of a Nested IF and a Vlookup, determine the correct shipping charge for the order.
3) Determine the total price of the order.
Attachment:- Assignment.rar