PDA

View Full Version : VBA code in Cash Flow forecast



bopha99
08-22-2017, 04:51 PM
Hello,

I am running a 10 year cash flow forecast where in each year there are forms of income and expenses. If expenses are greater than income, the difference gets taken from an account called taxable assets (see row 45 in attached excel file). My problem that I am trying to automate with a VBA code is that when the taxable assets line goes negative (see year 2021 and on highlighted in yellow), I would like the VBA code to identify the negative account balance and input an amount in the income section so that taxable assets remains above a certain amount, say $10,000. The largest account would be drawn upon first. Each year the VBA code would identify 1) if taxable assets are negative and 2) which is the largest account to pull from to make taxable assets stay about $10,000. If taxable assets is in between 0 and 10,000, then nothing will happen. Keep in mind that drawing from non-taxable assets will have to have a greater amount because of the tax rate. Also, once the account goes to 0, then the VBA code will recognize this and it will not be an option to draw from in the future.

I do not know much about VBA, but I would love for someone to be able to make such a code. Is this code possible? Please see attached and thanks in advance.

offthelip
08-23-2017, 02:00 AM
your requirements are not clear, but it should be quite easy to do what you want with vba if we can work out what you want.

I would like the VBA code to identify the negative account balance and input an amount in the income section ( which row??) so that taxable assets ( line 45 for that column?) remains above a certain amount, say $10,000. The largest account (which rows??) would be drawn upon first. Each year the VBA code would identify 1) if taxable assets are negative (line 45?)and 2) which is the largest account (which rows?) to pull (does this mean we subtract the amount entered in the income row?) from to make taxable assets stay about $10,000. If taxable assets is in between 0 and 10,000, then nothing will happen. Keep in mind that drawing from non-taxable assets ( where does this come in? which rows?) will have to have a greater amount because of the tax rate. Also, once the account goes to 0, ( what does this mean which rows) then the VBA code will recognize this and it will not be an option to draw from in the future.

mdmackillop
08-23-2017, 02:22 AM
Try this

Sub Test()
Dim r As Range, cel As Range, col As Range, x&, rw&
Set r = Range(Cells(45, 1), Cells(45, 1).End(xlToRight).End(xlDown)(0))
For Each cel In r.Rows(1).Cells
If cel < 0 Then
Set col = Intersect(cel.EntireColumn, r)
x = 10000 - cel
With Application.WorksheetFunction
rw = .Match(.Max(col), col, 0)
End With
With Cells(rw + 44, col.Column)
.Interior.ColorIndex = 7
.Value = .Value - x
End With
cel.Value = cel.Value + x
End If
Next
End Sub

bopha99
08-23-2017, 09:49 AM
Offthelip thanks for the response. Here are my answers:

I would like the VBA code to identify the negative account balance and input an amount in the income section ( which row?? rows 3-6 for accounts 1-4 and rows 13 through 19 for accounts 5-11) so that taxable assets ( line 45 for that column? yes line 45 and for the column where the taxable assets is negative in line 45) remains above a certain amount, say $10,000. The largest account (which rows?? the largest account in rows 46 through 56 in the year/column when taxable assets turns negative ) would be drawn upon first. Each year the VBA code would identify 1) if taxable assets are negative (line 45? yes)and 2) which is the largest account (which rows? rows 46 through 56) to pull (does this mean we subtract the amount entered in the income row? any amount entered in the income rows (3-6 or 13-19 will automatically be entered as a negative in rows 32-42...the accounts in rows 46-56 read off of rows 32-42 and incorporate the income/withdrawals plus investment return) from to make taxable assets stay about $10,000. If taxable assets is in between 0 and 10,000, then nothing will happen. Keep in mind that drawing from non-taxable assets ( where does this come in? which rows? drawing from non taxable assets will be in rows 3-6...this will then be entered as a negative in rows 32-25 in which the account values (rows 46-49) will read from...the manual part to the spreadsheet is entering in the correct amount in rows 3-6 and 13-19 to ensure taxable assets (row 45) stays positive/above 10k ) will have to have a greater amount because of the tax rate. Also, once the account goes to 0, ( what does this mean which rows...rows 46 through 56...they will be reading from rows 32-42 and so when accounts in rows 46-56 are close to 0 then the VBA code will stop drawing income from those accounts ) then the VBA code will recognize this and it will not be an option to draw from in the future.

Thanks for the help. Please let me know if you have more questions.

bopha99
08-23-2017, 09:58 AM
MDMackillop,

Thanks for the response and the test code. I think this code is on the right track, but I'd need to see the income coming in in rows 3-6 for non taxable assets and rows 13-19 for taxable assets. If there is income coming in from an account it would be considered spending and that amount would be negative in rows 32-42. The accounts in rows 46-56 would read from the spending in rows 32-42 and would thus go down by the amount needed to keep taxable assets (row 45) positive and increase by the rate of return for the year. In your code, it is unclear how much is being drawn from account 4 (row49). The code would need to show the exact amount being drawn in row 6 that would be able to keep row 45 at 10,000 or above. I hope this makes sense. Do you see a better way of making this VBA code? Thanks in advance.

bopha99
08-23-2017, 10:21 AM
To all,

What would have to be "hard coded" by the VBA code would be rows 3-6 and 13-19...everything else would stay the same...the VBA code would have to hard code in rows 3-6 and 13-19 the amount necessary to keep taxable assets (row 45) above 10k. Is this possible to make? Thanks in advance.

offthelip
08-23-2017, 10:34 AM
That explanation really helped , I am not an accountant I know nothing about the US tax system.
try this :


Sub balance()


For i = 2 To 11
inarr = Range(Cells(1, 1), Cells(57, 11))
If inarr(45, i) < 0 Then
' we need to balance
' find largest account
maxb = 0
maxro = 0
For j = 45 To 56
If inarr(j, i) > maxb Then
maxb = inarr(j, i)
maxro = j
End If
Next j
If maxro < 50 Then
'not taxable so dived by .7
balamount = (10000 - inarr(45, i)) / 0.7
accro = maxro - 43
Else
balamount = (10000 - inarr(45, i))
accro = maxro - 37
End If
Cells(accro, i) = balamount
End If
Next i
'
End Sub

bopha99
08-23-2017, 11:06 AM
wow great. A couple of questions. 1) If I were to add a tax calculator that calculates the taxes paid in row 10 so that the tax rate was constantly changing based upon the income received (and not just stagnant at 30%) would this be possible? Basically, what I would have is another worksheet that calculates the taxes based upon income tax brackets and where the person lived which would then feed back to row 10. 2) Does the VBA code know what to do if all accounts were insufficient to cover the amount necessary to make taxable assets line 45 stay above 10k ? In this case, it should draw upon all accounts to bring all forms of income in in rows 3-6 and rows 13-19 and make taxable assets 0 and all accounts 0 from there on out. Would this be possible? Thanks for your help.

offthelip
08-23-2017, 11:30 AM
Having a separate calculation to work out the tax rate will work provided it is not a continous loop, ie if changing the amount the code puts in row 3 to 6 , changes the tax rate calculation you will have an infinite loop , so that won't work. But the code does need to know what the tax rate is, either by picking it up somewhere or being able to calculate it.

The code doesn't take any account of accounts which have insufficient funding , it just picks the largest all the time, and assumes there are enough funds
it is quite easy to put a check in to check that Maxb > balamount and if it isn't put another bit code in .
I don't have time for that at this minute, may be tomorrow, unless somebody else wants to chip in.

bopha99
08-23-2017, 01:16 PM
ok sounds good. So, if I had a very basic tax bracket, would that be considered an infinite loop? How would the code be able to calculate the taxes without it being an infinite loop?

That's great. I can wait or see from whoever wants to contribute to the code. I ask this because when I make living expenses $100,000 and have all the accounts much smaller values, the code will pull from the highest account, but the code will over pull from the highest account so that the highest account will now have a negative value (see orange cells in attached spreadsheet). Is there a way for the code to only pull from an account to make it go to 0 and then if taxable assets are still negative to pull from the next highest account to keep taxable assets above 10,000? It would be some sort of sequence where it pulls until the account goes to 0, and then it pulls until the next account goes to 0, until taxable assets are 10,000. Does this make sense? Thanks. This has been very helpful.

offthelip
08-23-2017, 03:29 PM
I have modified the code so that it won't send an account negative and it will loop through all accounts until either it succeeds with the balance or they all end up at zero


Sub balance()

' do all the years
For i = 2 To 11
' loop through all the accounts
For ii = 1 To 11
inarr = Range(Cells(1, 1), Cells(57, 11))
If inarr(45, i) < 0 Then
' we need to balance
' find largest account
maxb = 0
maxro = 0
For j = 45 To 56
If inarr(j, i) > maxb Then
maxb = inarr(j, i)
maxro = j
End If
Next j
If maxb > 0 Then
If maxro < 50 Then
'not taxable so dived by .7
balamount = (10000 - inarr(45, i)) / 0.7
accro = maxro - 43
Else
balamount = (10000 - inarr(45, i))
accro = maxro - 37
End If
If balamount > maxb Then
Cells(accro, i) = maxb
Else
Cells(accro, i) = balamount
End If
End If
End If
Next ii
Next i
'
End Sub





A simple tax bracket calculation should be fine.. If necessary we could iterate around the tax calculation as well.

bopha99
08-23-2017, 05:10 PM
Wow looking great! What do you mean by we could iterate around the tax calculation? Also, once all accounts are exhausted, can you make the taxable assets line read 0 too going forward so that there is no growing negative balance in taxable assets? See attached.

offthelip
08-24-2017, 02:26 AM
I would change the formula in row 45 to ensure the taxable assets are zero once acccounts are exhausted by changing the equation in C45 to:

=IF(AND(FV($E$63,1,-C28,-B45)<0,SUM(C46:C56)<0.01),0,FV($E$63,1,-C28,-B45))

then copy this across the rest ofthe row

In terms of the tax calculation, where the accounts have enough to get a balance, I could start by assuming the highest rate of tax, put that in row 6 to, then if the taxable asset were above the 10000 which I am aiming at because the actual tax was lower, I could then reduce the amount added by the amount over 10000 and that will bring it to something below 10000, but probably not exactly 10000

bopha99
08-24-2017, 10:54 AM
Ok yes...an if statement would work there. I'm not sure what you are talking about with regards to the tax. It sounds like would this be an iterative process? Regarding the taxes, would it be possible to have the VBA code read from a template spreadsheet where I would input the state tax rate as a single percentage and then whether or not the person was single/married and lived in Canada or the U.S. (this would be used for the federal tax rate calculation)? I would then have 3 tax brackets that the VBA code would read from depending on the inputs in the template sheet (single/married, US/Canada) (the tax brackets would be for single/US, married/US, and both/Canada). Based on what was inputted in the templates sheet, the VBA code would know which tax bracket to use to calculate the federal tax. The state tax would then be row 8 times the state tax rate (there is no tax bracket here). The federal tax would then be a calculation the VBA code would do based on the inputs from the template, the tax brackets, and the taxable income from row 8. The total tax would then be the state tax plus the federal tax. Would this be possible? I have attached the tax brackets that would be used in the VBA code. Thanks in advance for your help.

offthelip
08-24-2017, 02:43 PM
This has ended up being a completely different problem to where we started, what is called mission creep!!
I think you can implement those tax calculations purely using equations in excel. Once you have done that I suggest you save the actual percentage rate of tax in row 9, then in my code you can change the value "0.7" to (1 - inarr(9,i) and it will then use that percentage in the vba calculation.

bopha99
08-24-2017, 03:04 PM
This has ended up being a completely different problem to where we started, what is called mission creep!!
I think you can implement those tax calculations purely using equations in excel. Once you have done that I suggest you save the actual percentage rate of tax in row 9, then in my code you can change the value "0.7" to (1 - inarr(9,i) and it will then use that percentage in the vba calculation.

Ok thanks for that. I will try adding in to the VBA code. One other question I have for this code in particular that shouldn't be too hard to answer/modify the VBA code is:

Say I have more than one of these cash flows in a workbook. What can you add to the VBA code to make it so that the VBA code will run through all the worksheets in the workbook? Thanks in advance.

offthelip
08-24-2017, 03:21 PM
to loop through all worksheets this will do:


Sub loopws()


Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Call balance
Next ws

End Sub

bopha99
08-24-2017, 03:46 PM
Wow great. Thanks! One similar question...if I had several workbooks with multiple of the same cash flow worksheets but with different sources of income, would there be a simple/separate VBA code that I could run that would then identify the names of each workbook and run the VBA codes in each of the workbooks without having to open each excel file? What I'm trying to do is graph row 57 (total liquid assets) of multiple cash flows in multiple workbooks, but I don't want to have to manually open up each workbook and run the VBA code. Is there a VBA code to run the VBA codes in each workbook?

bopha99
08-24-2017, 03:51 PM
And I guess the VBA code would have to refresh the workbooks as well since I plan on having one template that feeds all workbooks. Is this possible without having to open up each excel workbook?

mdmackillop
08-25-2017, 01:46 AM
For the purposes of the forum, please post the last requests as a new question.

bopha99
08-25-2017, 09:27 AM
Ok no problem. Thanks for the heads up

bopha99
08-28-2017, 10:54 AM
Offthelip,

If I were to add another 10 years to the worksheet, what would have to change in the VBA code for the code to run years 11-20? Please see attached. Thanks.

bopha99
08-29-2017, 04:56 PM
Offthelip,

When I have income values that are already coming in from accounts 1-11 (rows 3-6 and rows 13-19), the VBA code does not seem to adjust for those income values. For instance in the attached spreadsheet in tab cash flow (3), if I run the VBA code as is where it is clear that money should be taken from account 1 to make taxable assets stay positive, it looks like it over pulls income. For instance taxable assets in years 2018, 2020, 2024 go to 64k, 91k, 87k. Why is it over pulling? Can you make it only take what is necessary to get to $10k? Thanks in advance.

offthelip
08-30-2017, 01:41 AM
You are trying to develop what is obviously a fairly sophisticated tax forecast system. I answered your original question and a number of follow up questions. These last two requirements were not in your original requirements, I would be surprised if these last two were the last "additional requirements" for your system. I am sorry but I don't have the time or the inclination to develop this any more. I suggest you either learn to do it yourself by studying VBA more or else you pay somebody to do it for you. This is supposed to be a VBA forum to answer VBA questions, which I am happy to help with.
Just to give you a clue, it would appear that you need to add the amounts in rather than writing them in.

bopha99
08-30-2017, 04:28 PM
ok. thanks for your help. I am glad to have gotten this far with the code.