134 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL
where
k=periodicity of the payments
n=total periods
PVCFt=present value of the cash flow in periodtdiscounted by the yield
Model Builder 8.6: Calculating Modified Duration
1.On the Analytics Sheet, label cell B8:Modified Duration. The formula for
modified duration translates easily into Excel. In cell E8 enter:
=1/((1+E5/12))*(SUMPRODUCT(E16:E375,$A$16:$A$375)/(12*E4))
2.Most of the formula is self-explanatory with the written equation presented
earlier. The one item to notice is that the SUMPRODUCT function is used on
the present values of the cash flows for more efficient calculation. Copy and
paste this formula over the range E7:G7. The final Analytics section should look
like Figure 8.6.
FIGURE 8.6 The analytics sheet is now complete.
