Financial model - cashflow help

Hi,

I am building a cashflow projection in Excel for a client and I am struggling with how to deal with repayment plans in the cashflow statement. Some customers will pay for their product in 12 equal instalments over 12 months.

Basically what I need is a formula that allows me to sum the values in 12 cells in a row (each cell is a month), ending with the current cell/month. So the maximum length of the sum is 12 cells. At the end of 12 months, the sum needs to start at the next cell along. For example, if the repayment plan starts in January 2020 and the first value is in Cell A1, the calculation needs to start in Cell A1 and end in Cell L1, being 12 months. Then the next calculation, needs to start in Cell B1 and end in Cell M1 and so on.

The projection is for a number of years so manually moving the cell along each month is not practical.

Can anybody please help?

Hi
we need to see the file in order to create the formula.

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

Thank you, attachment below. In this example the client is selling £10,000 worth of products each month, all of which are to be paid over a 12 month period. It is row 11 that I need the formula for please. I have put the manual formula as a reference.

If possible it would be great if the formula could deal with varying length payment plans ie. there would be a separate cell where the repayment plan length could be changed, so that we coulld see the impact on cashflow if for example the repayment period changed to 18 months.

My apologies, there are actually a number of errors in that example. I have re-attached a revised example, which hopefully makes more sense.

Month 1, sales of 10,000 are made, 1/12 of that is paid in that month which is shown in B10, and then remaining payments are taken monthly over the next 11 months.

Month 2 and every month after that sales of 10,000 are again made, which is why the deferred amount doesn't reduce. So in month 2, there are now 2 payments of 833, one from month 1 and the other from month 2, so the total cashflow for the month is 1,667.

Month 12 is the final payment of the sales from month 1 ie they have been fully repaid, so in the month 13 the 833 from month 1 should no longer be counted as a cashflow. What I need is a formula that knows that at the 12 months, no further payments will be collected.

Hopefully I have explained it better this time.

See the attached revision to your workbook.

I treated cell M13 as the number of months in which equal payments are received for the new monthly sales in row 7. I changed the adjustments in row 8 to use the entry in M13.

I expanded the month indices in row 5 out to 72 months. I changed the new monthly sales in row 7 to increase by 1,000 per month in order easier to be able to see the detailed cashflows.

B21:BU81 shows payment inflows for each sales month in a separate row. A21:A81 are month indices used in the formulas in B1:BU81. B19:BU19 are sum of payments for all sales months received in a given calendar month. Those are presumably the values you want in row 10.

B17:BU17 are the single-cell formulas which should produce the calendar month cashflows you're seeking for row 10. A17 is the total squared deviation between B17:BU17 and B19:BU19. It should be 0.

Originally Posted by hrlngrv

See the attached revision to your workbook.

I treated cell M13 as the number of months in which equal payments are received for the new monthly sales in row 7. I changed the adjustments in row 8 to use the entry in M13.

I expanded the month indices in row 5 out to 72 months. I changed the new monthly sales in row 7 to increase by 1,000 per month in order easier to be able to see the detailed cashflows.

B21:BU81 shows payment inflows for each sales month in a separate row. A21:A81 are month indices used in the formulas in B1:BU81. B19:BU19 are sum of payments for all sales months received in a given calendar month. Those are presumably the values you want in row 10.

B17:BU17 are the single-cell formulas which should produce the calendar month cashflows you're seeking for row 10. A17 is the total squared deviation between B17:BU17 and B19:BU19. It should be 0.

Great, thank you so much!