Creating a Cash Flow Reserve

[mdelicata]

Hi, I'm trying to create a cash flow reserve where cash flows are swept each month until the reserve amount is reached.

I've attached a picture of what I'm describing.

Book1.jpg


I need a formula to calculate the "Reserved" amount each month that will take Cash Flow until the reserve amount it met.

Thanks for the help!

-Matt

[vba_php]

your requirements aren't specific enough to describe the issue, Matt. furthermore, the 2nd image is way too small to see. can you provide any more details?

[mdelicata]

Thanks for the response/ feedback Adam, and I apologize for the lack of clarity... I'm new to using this forum.

The attached picture is actually the same image as the one embedded within my post.

I highlighted in yellow the line I'm working on.

I'd like to have a formula that "draws down" cash flow each month until it reaches the reserve amount ($200,000 in my example).

Said differently, if you have $20,000 in cash flow each month, you would reach the reserve in month 10, and the cash flow in month 11 would flow through past the reserve line item.

Another example would be if you have $15,500 in cash flow each month, you would reserve that cash flow up to month 12 (12 x 15,500 = 186,000), and then in month 13, $14,000 would be taken for the reserve and the remaining $1,500 would flow to the next line.

But I'm having trouble writing a formula that takes the cash flow each month right up to exactly the reserve amount.

Does that provide enough clarification?

Thanks again.

-Matt

[mdelicata]

I've attached a sample excel sheet. I hope that helps.

Thanks,

Matt

[mdelicata]

Adam, Any chance you've taken another look at my problem? I'm really stuck on this.

Thanks!

-Matt

[vba_php]

let me take a look. might not be able to get back to you until later on today though.

[vba_php]

Matt,

I have taken a look at your file and I need clarification on the following things that you have said. First, this:

Thanks for the response/ feedback Adam, and I apologize for the lack of clarity... I'm new to using this forum.
I'd like to have a formula that "draws down" cash flow each month until it reaches the reserve amount ($200,000 in my example).

I understand what you have said, however a little clarification is needed, and I'm not really sure what I need to know. sorry. And regarding this:

Another example would be if you have $15,500 in cash flow each month, you would reserve that cash flow up to month 12 (12 x 15,500 = 186,000), and then in month 13, $14,000 would be taken for the reserve and the remaining $1,500 would flow to the next line.

so, I understand what you are trying to do, and I don't think you can display the info that you want with just one single formula. but keep in mind that I am not an interface expert. I write code. So, to better understand what you're doing, that is if no-one else joins this discussion, is to ask these questions:

=> do you want to see the **difference** (subtraction) between the reserve and another amount (fixed or not) after each cash flow?
=> what do the monthly ""cash flows"" have to do with the reserved amount shown on your sheet (the numbers are the same), and in turn, how are they related to the overall Reserve Amount as the total?

thanks. I ask this because I am not a financing expert. You're talking to a coder. So the struggle is always real regarding communicating back and forth between business people and code writers. that is what's happening here. but don't worry, it'll work out just fine.

[mdelicata]

I've update the excel example... hopefully that helps.

In short, I collected monthly cash flow from operations, but the bank requires us to reserve a certain amount before we can distribute cash flow to investors, so I'm trying to write a formula that automatically calculates how much cash flow is needed each month to get to the reserve amount. So if the reserve amount changes, I can just change the reserve amount and the monthly amounts change.

[vba_php]

oh I see OK! so....here's the logic:

=> cash flows = inflows to the bank (income)
=> reserve amount = regulatory requirement to guarantee solvency after throw distributions to investors

is this correct? I need these questions answered, primarily because I've done a lot of work today and I'm not a finance person. the other question that needs to be answered is:

does the ""reserve amount"" change every month? or can it?

if the reserve amount does change every month, how is it calculated?

you have never provided the regulatory policies that guide question #2 above. that would drive the software answer for you, I would think.