Calculating deferred interest payments with compounding interest

[diananemo]

I've googled this without much luck. I've inherited a spreadsheet and I need to optimize it in order to extend its functionality without it breaking or becoming totally unwieldy. This is a worksheet that calculates payments due on a loan. The loan is not due until the property is sold. A portion of the interest is payable every year, and a portion of the annual interest payment is deferred. But that deferred interest payment amount gets added to the outstanding principal, so the next year's calculation of interest owed is based on a higher principal balance. The way this is done currently (what I inherited) has one row for every year allowing up to a 30 year loan.

It seems to me that if I could write this as a recursive function, I could do this with one row (assuming no prepayments). Also, this does not use IPMT, and I think it probably should.

See attached spreadsheet, and look at the 'If No Prepay' tab, starting at row 21. (Note that it seems some of the labeling indicates that this is compounding monthly. Doesn't matter, the ideas are the same, and their business rules may have changed since this particular tab on the spreadsheet was in use).

Many thanks in advance.

-Diana