Question

NEEDS TO BE DONE IN EXCEL.

A price level adjusted mortage (PLAM) is made with the following terms:

Amount=$95,000

Initial interest rate= 4 percent

Term= 30 Years

Points= 6 percent

Payments to be reset at the beginning of each year.
Assuming inflation is expected to increase at the rate of 6 percent per year for the next five years:
a. Compute the payments at the beginning of each year (BOY)
b. what is the loan balance at the end of the fifth year?
c. what is the yield to the lender on such a mortgage?

Answer 1

• Step 1 :

  Price level adjusted mortgage (PLAM) is a kind of mortgage where the mortgage payments get adjusted for inflation. The new mortgage payments after the adjustments are computed using the adjusted balance. In this kind of mortgages interest rate does not get varied while the principal changes. Usually the changes are reflected by index-CPI. For implementation of these changes the intervals are agreed upon by lender and borrower at frequent intervals.

  As PLAM is a kind of remedy used to imbalance the problems for the savings institutions. In order to help reduce the risks associated with uncertainties of inflation and its impact over interest rates, it is suggestive for the lenders to originate mortgages at interest rates that which reflect the expectations of real interest rate in addition to a risk premium. This is needed to avoid the likelihood of loss that occurs due to getting defaulted upon a mortgage taken.

• Step 2 :

  The mortgage amount is $95,000; initial interest rate is 4%, compounded monthly; the points are 6%; period is 30 years and inflation rate is 6% for next five years.

• Step 3 ;

  a) Calculate the payments at the beginning of each month for five years are as follows:

  

  

• Step 4 : Formulas for above values are as follows:
• 

• 

• Step 5 :

  The following table shows the yearly payments as follows:

  

• Step 6 :

  (b)

  From the above calculation, it can be stated that the loan balance at the end of fifth year is.

• Step 7:
• C) Calculate the yield to the lender on the mortgage using MS-EXCEL “IRR” Function as follows:

• 

  

  Explanation:

  IRR can be calculated using Excel “IRR” function. The Excel steps to calculate IRR function are as follows:

  • First go to the Menu bar of Excel and select ‘Formulas’ option

  • Select Insert Function’(fx)

  • Then select category as Financial

  • Then select “IRR” and click OK

  • Then the Function Argument window will open. Now, input the given data in the required field

  • Click OK

  • The formula will display the final answer as 0.91%

• Step 8 :

  Calculate the effective annual yield on mortgage as follows:

  

  Therefore, the effective annual yield on mortgage is .

• Step 9 :

  • Cash flow at 0^th month is -$89,300.

  • Cash flow at 61^st month is the outstanding balance of $114,987.22 .

I Have done your question ,,like me if you understand the solution.