Question

FINANCE ASSIGNMENT **THIS ASSIGNMENT REQUIRES THE USE OF MICROSOFT EXCEL** Refer to information page on the use of some finance features in Excel. Understand financial functions @PMT, @PPMT, @IPMT, @PV and @FV. See here. 1. Assume you are employed at $60,000 per year. Consider such deductions as social security, income taxes (federal, state and county) and your payment on health benefits. Do the best you can to come to grips with what is a realistic monthly take home pay. 2. Develop a projection of monthly expenses; food, clothing, housing, auto payment, auto insurance, gasoline, utilities, etc. (refer to the list of possible expenses outlined in the Money Management PowerPoint presentation). Use Excel to organize your list of projections. 3. Use Excel to determine the above monthly auto payment and possible housing payment. Let’s try a scenario that will help you to figure out what payment is best for you. A. First find the monthly payment on a $10,000 automobile. Assume 6.5% over 36 months with no money down. B. Similarly, determine the same for a $30,000 automobile. C. Assume, as an alternative, you decide to wait for a year, and that you place whatever monthly payment you would be paying on either car into an interest bearing savings account, or small certificates of deposit paying 2.0% per month. Use the @FV function to determine this value. D. Now, patting yourself on the back for your self-restraint over the year, you take this amount and use it as a down payment to buy either car (except it is a year later). Assume the same terms: 36 months with 6.5% interest. Again, calculate the new monthly payment and compare with A and B. How much extra "luxury income" do you have per month as a benefit of your selfrestraint. E. Surrounding Morgan State University, $750/month buys an adequate apartment. I assume, even with this $750, you could still swing the $30,000 automobile in B. However, if you go for option A and take the savings between B and A, you have that much more (than the $750 you would have to pay for an apartment) that you could use to purchase a modest home. Use Excel to determine how much house you could buy with $750 + (Payment in B- Payment in A). Assume 6.0% over 360 months. F. Assume a loan of $30,000, 36 months, 6.5% interest. Generate a table of payments showing the principal payment and the interest payment for each of the 36 months. 4. After all monthly expenses are accounted for, place 5% of the balance in your savings account. How much money will you have left from your take home pay? 5. Be sure to list your projected monthly expenses clearly on your Excel spreadsheet. Include your chosen monthly automobile and housing payment calculated in the above steps as well as your savings amount. You must clearly highlight your choice of car (purchased with/without a down payment) and a modest home or apartment. References = pv(rate, nper, pmt) Please see PV Function Help = pmt(rate, nper, pv) Please see PMT Function Help = ppmt(rate, per, nper, pv) Please see PPMT Function Help = ipmt(rate, per, nper, pv) Please see IPMT Function Help =fv(rate, nper, pmt, [pv]) Please see FV Function Help

Answer 1