CODEWITHSUNDEEP
excelfinance
Jason
Jason

Reputation: 506

How to calculate the weighted average life for a mortgage in Excel with variable payment

I am trying to figure out how to calculate Present Value and Weighted-Average Life as of June 30, 2016 using the cash flows below for a mortgage with a rate of 7%.

For WAL, I tried interest payments / interest rate x Principle but that didn't work. I would appreciate any ideas.

Dates        Principal      Interest   Ending Balance
15-Apr-16    $-             $-           $10,000,000 
15-May-16    $50,000         $41,667     $9,950,000 
15-Jun-16    $50,000         $42,840     $9,900,000 
15-Jul-16    $50,000         $41,250     $9,850,000 
15-Aug-16    $50,000         $42,410     $9,800,000 
15-Sep-16    $50,000         $42,194     $9,750,000 
15-Oct-16    $50,000         $40,625     $9,700,000 
15-Nov-16    $50,000         $41,764     $9,650,000 
15-Dec-16    $50,000         $40,208     $9,600,000 
15-Jan-17    $50,000         $41,333     $9,550,000 
15-Feb-17    $50,000         $41,118     $9,500,000 
15-Mar-17    $50,000         $36,944     $9,450,000 
15-Apr-17    $50,000         $40,688     $9,400,000 
15-May-17    $50,000         $39,167     $9,350,000 
15-Jun-17    $50,000         $40,257     $9,300,000 
15-Jul-17    $50,000         $38,750     $9,250,000 
15-Aug-17    $50,000         $39,826     $9,200,000 
15-Sep-17    $50,000         $39,611     $9,150,000 
15-Oct-17    $50,000         $38,125     $9,100,000 
15-Nov-17    $50,000         $39,181     $9,050,000 
15-Dec-17    $50,000         $37,708     $9,000,000 
15-Jan-18    $50,000         $38,750     $8,950,000 
15-Feb-18    $50,000         $38,535     $8,900,000 
15-Mar-18    $50,000         $34,611     $8,850,000 
15-Apr-18    $50,000         $38,104     $8,800,000 
15-May-18    $50,000         $36,667     $8,750,000 
15-Jun-18    $50,000         $37,674     $8,700,000 
15-Jul-18    $50,000         $36,250     $8,650,000 
15-Aug-18    $50,000         $37,243     $8,600,000 
15-Sep-18    $50,000         $37,028     $8,550,000 
15-Oct-18    $50,000         $35,625     $8,500,000 
15-Nov-18    $50,000         $36,597     $8,450,000 
15-Dec-18    $50,000         $35,208     $8,400,000 
15-Jan-19    $1,000,000      $36,167     $7,400,000 
15-Feb-19    $1,250,000      $31,861     $6,150,000 
15-Mar-19    $1,250,000      $23,917     $4,900,000 
15-Apr-19    $400,000        $21,097     $4,500,000 
15-May-19    $2,000,000      $18,750     $2,500,000 
15-Jun-19    $2,500,000      $10,764     $-

Upvotes: 0

Views: 11285

Answers (1)

PaichengWu
PaichengWu

Reputation: 2689

img

  1. Cut dates before 2016/6/30, remain those greater than or equal 2016/6/30. (as above).
  2. make CF column, =C3+D3
  3. make no_of_years column, =(B3-$B$3)/365
  4. make CF*no_of_years column, =G3*F3
  5. use XNPV function to get Present Value, =XNPV(0.07, F3:F39, B3:B39)
  6. get WAL by =SUM(H3:H39)/SUM(F3:F39)

Upvotes: 2

Related Questions