Reference no: EM132443090

Use sufficient solution steps, and specific Excel functions =PV(...), =FV(...), =PMT(...), =NPER(...), =RATE(...), =PRICE(...) or =YIELD(...) whenever applicable. Don't use any  "Amortization tables" . Must be done in excel, put each case on a separate tab. I will tip very well if all criteria is met with quality.

Case 1: 

You apply for a 20-year, fixed-rate (APR 6.48%) monthly-payment-required mortgage loan for a house selling for $150,000 today.  Your bank requires 22% initial down payment of house value, and $3,000 closing cost (to be carried into loan balance and amortized later) when the loan is approved.

(a) What is your monthly loan payment if you stick to the mortgage deal till the end (assuming each payment is made at the end of each month)?

(b) 9 years after buying the house, what will be the remaining loan principal balance? (Please note again it is a monthly mortgage.)

(c) 9 years after buying the house, the loan market rate drops from 6.48% APR to 6.00% APR.  You plan to refinance, but the bank would charge an extra fee of $4,500 for refinancing (which is carried into the current remaining loan balance for amortization).  Would you be able, and by how much, to lower your monthly loan payment if you choose to refinance on the remaining loan principal balance over the remaining loan life period?  Based on your calculation results, should you choose to refinance or not?

(d)  Redo the calculations in Question (c), assuming that the loan market rate drops from 6.48% APR to 5.76% APR (instead of 6.00%).  Shall you choose to refinance then?

Case 2:

By the end of each year, you contribute a $3,300 to your retirement fund portfolio, which on average earns an annual nominal return of 11.25% over time.  The annual contribution continues for 36 years until you retire. (Note: All tax concerns are ignored here.)

(a) By the time of your retirement, how much money would you have in your portfolio? (without considering any inflation)

(b) For your post-retirement life (which would last approximately another 28 years), by the end of every year you withdraw and spend an equal amount of annuity payment from your retirement fund account.  What should be the annual payment amount you withdraw if you do not want to leave any money to your heirs? (without considering any inflation)

(c) Considering the long-term inflation averages 3.50% annually.  How much money (at real purchasing power) will you actually have when you retire?  How much equal amount should you withdraw and spend (at real purchasing power) by the end of each year for your post-retirement life, provided that you leave no money to your heirs in the end?

(d) Again consider the long-term annual inflation 3.50%, and use the information you obtain from the above (c).  How much equal amount should you and your heirs withdraw and spend (at real purchasing power) by the end of each year, provided that you and your heirs can benefit from this fund generations after generations infinitely?

Case 3:

You find a municipal bond issued by the Illinois state government. 

The last sale "settlement date" is June 28, 2014, on which the bond price is quoted as "109.171" (Note: Bonds in US market are not quoted in $, but instead in % of par value). 

The bond's coupon rate is fixed as "5.000" percent per year (coupon paid semiannually), and its yield to maturity (YTM) is quoted as "3.818" percent per year.     

The bond's maturity date is October 01, 2051.

(a) Based on the aforementioned settlement date, maturity date, coupon rate, coupon frequency and yield to maturity, what shall be the corresponding bond PRICE (in term of "% of par value")?  Does your PRICE solution match the market quoted price?

(b) If the Fed tightens its monetary policy now, the financial market interest rates generally rise, and thus the aforementioned bond's YTM also rises to "4.318" percent per year.  Will the corresponding bond PRICE go up or go down then?  By how much?

(c) If the Fed loosens its monetary policy now, the financial market interest rates generally drop, and thus the aforementioned bond's YTM also drops to "3.318" percent per year.  Will the corresponding bond PRICE go up or go down then?  By how much?

(d) Based on your answers to (b) and (c), is there a positive, negative or zero association between bond YIELD and its PRICE?  What kind of risk effect is this called?