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
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 | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
1 | 1/31/2022 | 2/28/2022 | 3/31/2022 | 4/30/2022 | 5/31/2022 | 6/30/2022 | 7/31/2022 | 8/31/2022 | 9/30/2022 | 10/31/2022 | 11/30/2022 | 12/31/2022 | 1/31/2023 | 2/28/2023 | ||||||
2 | Purcahse Date | Term Start | Term End | Amount | ||||||||||||||||
3 | 1/17/2022 | 2/1/2022 | 1/31/2023 | 12,000.00 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | ||||
4 | 4/15/2022 | 2/1/2022 | 1/31/2023 | 3,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | |||||||
Sheet1 |