Prepaid Amortization formula help

bobby_smith

Board Regular
Joined
Apr 16, 2014
Messages
90
Good day,
I would like some assistance with creating a formula to automatically populate the colums below from E to R.
My biggest challenge is the second scenario where the payment date is after the term start date.
Since the item was paid for in April but the term start date was February, 3 months of expense must be booked in April for (Feb, Mar, April).
Please note an item should be treated with full amortization expense in a month, for example if the monthly amount is 1,000, it does not matter if the start date is the 1st, 5th, 16th, 23rd etc. The monthly amount will still be 1,000


Book1
ABCDEFGHIJKLMNOPQR
11/31/20222/28/20223/31/20224/30/20225/31/20226/30/20227/31/20228/31/20229/30/202210/31/202211/30/202212/31/20221/31/20232/28/2023
2Purcahse DateTerm StartTerm EndAmount
31/17/20222/1/20221/31/202312,000.001,0001,0001,0001,0001,0001,0001,0001,0001,0001,0001,0001,000
44/15/20222/1/20221/31/20233,0001,0001,0001,0001,0001,0001,0001,0001,0001,000
Sheet1
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
I was able to get the solution so just posting for future reference
Excel Formula:
=IF(OR(EOMONTH(E$1,0)>EOMONTH($C3,0),EOMONTH(E$1,0)<EOMONTH(MAX($A3:$B3),0)),"",$D3*IF(EOMONTH(E$1,0)<>EOMONTH(MAX($A3:$B3),0),1,12*(YEAR(E$1)-YEAR($B3))+MONTH(E$1)-MONTH($B3)+1)/(12*(YEAR($C3)-YEAR($B3))+MONTH($C3)-MONTH($B3)+1))
 
Last edited by a moderator:
Upvote 0
Solution

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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
Back
Top