时间戳

首页 > 解决方案 > 如何在Excel中计算可变付款抵押贷款的加权平均寿命

excel - 如何在Excel中计算可变付款抵押贷款的加权平均寿命

问题描述

我试图弄清楚如何使用下面的现金流来计算截至 2016 年 6 月 30 日的现值和加权平均寿命，利率为 7%。

对于 WAL，我尝试了利息支付/利率 x 原则，但没有奏效。我会很感激任何想法。

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     $-

标签： excelfinance

解决方案

图像

  1. 2016/6/30 之前的截止日期，保持大于或等于 2016/6/30 的日期。（如上）。
  2. 制作CF柱，=C3+D3
  3. 制作no_of_years列，=(B3-$B$3)/365
  4. 制作CF*no_of_years列，=G3*F3
  5. 使用XNPV函数获取现值，=XNPV(0.07, F3:F39, B3:B39)
  6. 获得WAL _=SUM(H3:H39)/SUM(F3:F39)

推荐阅读