Interest Bucket

[supradude]

I have unfortunately spent more time on this than I intended before coming to the realization I need help. What I am trying to accomplish is to create a spreadsheet if someone has a loan and skips a payment to show different scenarios. For example:

Loan Amount	$100,000
Regular Payment	$722.67
Next Payment Due Date	05/21/2020
Current Remaining Amortization	19.67 Years
Current Maturity Date	04/01/2040

There are several options I need help figuring out.

1. How long it will take with making regular payments once payments recommence. Interest will continue to accrue regardless if skipping a payment or not. If someone skips a payment for three, or even six months, that when making the same $722.67 payment, how do I calculate how many months before it will begin chipping away at principal again if kept at the same amortization (they will simply have a balloon payment at the end of the amortization).

2. If the above has the amortization extended based upon the current $722.67 payment, how do I show what the new maturity will be on either three or six months of skipped payments (I get stuck in a circular reference on this)?

3. If capitalizing interest, what would the new payments be for either three or six months of skipped payments if kept on the same amortization?

4. If capitalizing interest, what would the extended amortization be if based upon the same $722.67 payment (I get stuck on circular reference again).

I know this is a lot to ask, so any help on even just one of these questions would be greatly appreciated.

 

[BadgerRoad]

In my opinion, the easiest approach is to create an amortization schedule. You can then adjust payments to zero for skips, use What If analysis to calculate new payments with different options, etc.