Interest Bucket

supradude

New Member
Joined
Apr 14, 2016
Messages
13
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 Date05/21/2020
Current Remaining Amortization19.67 Years
Current Maturity Date04/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.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

BadgerRoad

Board Regular
Joined
Dec 2, 2005
Messages
225
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,974
Messages
5,627,949
Members
416,283
Latest member
casea14

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top