EMI Demystified and relationship with PVIFA

Excel simply will not scale like code ! So here is the derivation from the fundamentals; Enjoy:
Geometric series 1+x+x^2+x^3+...= 1/1-X if X<1
PVn = A/R - A/[(1+R)^n.R]
e.g. PV3 = PV(infinity) - A/[(1+R)^3.R] where PV(Infinity) = A/R; remember D1/Ke-g
FV=PV(1+R)^N + PMT[(1+R)^N -1]/(1+R)^N*R)
pvn =" A/i" N=" Periods," R="Rate," A =" PMT"

FV="O"
- PMT = PV*R(1+R)^N/((1+R)^N -1)

disc = (1 + coupon) ^ N

payment= Bal * coupon * disc / (disc - 1)

InterestCashFlow(period) = Balance(period - 1) * Coupon / Frequency
PrincipalCashFlow(period) = payment - InterestCashFlow(period)
Balance(period) = Balance(period - 1) - PrincipalCashFlow(period)

The diagrams are simplified and do not denote the complexity of Net Interest Margins of different assets e.g. Senior Notes

Assets are valued by Sellers/Securitizers based on:
1. Interest
2. Default
3. Recovery rates
4. Credit spreads
5. Prepayment rate