Create a interest payout calendar which can sorted by date/month

Hello

I am trying to create an interest payout calendar for my elderly parents for bank term deposits. This will help in understanding when they will receive money and plan their spending.

I have populated an excel with dummy data, and in the desired output I have created the headers for information I need in the calendar.

Notes:

Interest payment can be quarterly/monthly/half yearly/yearly.

For example: if a deposit is made on 1st Jan 2020 with a quarterly payout for 120 months, then there should be an entry in the payout calendar on
1st April 2020,
1st July 2020,
1st October 2020,
1st January 2021,
1st April 2021,
1st July 2021,
1st October 2021,
1st January 2022, and so on until 120 months is reached from the date of deposit. The amount of interest payout for each payment would be yearly (interest/4 because it is a quarterly payout).


I hope I was able to describe the problem. I don't know how to make excel populate these payment data into a new table.

Any kind of help would be appreciated.

Thanks

Hi ChainRxn,

i have added a next paymentdate to your table. with this formula

Formula:

Please Login or Register  to view this content.

Then I put the output in a Pivot table. this will always show the next paymentdate for all acc no..
This calender should always be up to date.

see attachment.

Hi Roel

Thank you for helping me out. While that formula shows it next payment date for every deposit, it doesn't solve my problem to see all the future payment dates for all the deposits.

Ideally, I would like to pivot the table that shows all the upcoming payments for all the deposits. It would look something like this:

Table headers:
Date
Bank
Deposit AC number
Payout

It is this table that I want to pivot to create the calendar and apply various slicers. I have slightly edited your excel sheet.