Solve for X POSSIBLE ?? (1 Viewer)

[WineSnob]

I have searched and searched and have ALMOST concluded what I need is not possible in VB. I hope I am wrong and someone can help.
Here is what I am trying to do.
Solve for X where all the items in red are parameters. I put this equation in an online algebra solver and I get the correct answer (X=8000). How would I write this in VB? I would like to return X or 8000 to set a control value on the same form where the parameters come from. It is a reverse engineering thing. I have the code to get to 1232132.81 from 8000. It works fine. Now I need to provide 1232132.81 to get 8000.
1232132.81 = (x*(1-(1+(0.0008333))^-48)/(0.0008333))+(((x*60)*(1+0.04)^-4)/60)*(1-(1+0.0008333)^-60)/0.0008333+(((x*84)*(1+0.04)^-9)/84)*(1-(1+0.0008333)^-84)/0.0008333

 

[CJ_London]

It is only basic algebra after all (OK, quite complex) but should be able to reverse engineer to get the X on the left of the = sign - I presume the calculation remains the same, but the varables in red can change
1232132.81 = (x*(1-(1+(0.0008333))^-48)/(0.0008333))+(((x*60)*(1+0.04)^-4)/60)*(1-(1+0.0008333)^-60)/0.0008333+(((x*84)*(1+0.04)^-9)/84)*(1-(1+0.0008333)^-84)/0.0008333

I don't have time to check the solution but note that 0.0008333 is a recurring theme and there are 3 basic components added together.

So reversing the formula (I find it easier working left to right)

((x*(1-(1+(0.0008333))^-48)/(0.0008333))+(((x*60)*(1+0.04)^-4)/60)*(1-(1+0.0008333)^-60)/0.0008333+(((x*84)*(1+0.04)^-9)/84)*(1-(1+0.0008333)^-84)/0.0008333) =1232132.81

Then simplying brackets

x*(1-(1+(0.0008333))^-48)/(0.0008333) + x*60*(1+0.04)^-4/60 * (1-(1+0.0008333)^-60)/0.0008333 + x*84*(1+0.04)^-9/84)*(1-(1+0.0008333)^-84/0.0008333)=1232132.81

Then combining

x*((1-(1+(0.0008333))^-48)/(0.0008333) + 60*(1+0.04)^-4/60 * (1-(1+0.0008333)^-60)/0.0008333 + 84*(1+0.04)^-9/84)*(1-(1+0.0008333)^-84/0.0008333))=1232132.81

Then final solution

x=1232132.81/((1-(1+(0.0008333))^-48)/(0.0008333) + 60*(1+0.04)^-4/60 * (1-(1+0.0008333)^-60)/0.0008333 + 84*(1+0.04)^-9/84)*(1-(1+0.0008333)^-84/0.0008333))

Hopefully this should be the final answer but as I said haven't had the time to check, paste it into excel and replace the ^ with the appropriate Excel for power

 

[WineSnob]

Thanks for trying.
I was able to get it to work for the first 2 segments using the different numbers .

Segment 1 = 376268.05
Segment 2 = 400055.08 SEg1+Seg2 =776323.13
Segment 3 = 455809.68
Total = 1232132.81

I put the following in excel to check the results.

=376268.05/((1-(1+(0.0008333))^-48)/(0.0008333))works returns 8000

=776323.13/((1-(1+(0.0008333))^-48)/(0.0008333)+60*(1+0.04)^-4/60*(1-(1+0.0008333)^-60)/0.0008333) works returns 8000

=1232132.81/((1-(1+(0.0008333))^-48)/(0.0008333)+60*(1+0.04)^-4/60*(1-(1+0.0008333)^-60)/0.0008333)+84*((1+0.04)^-9)/84*(1-(1+0.0008333)^-84)/0.0008333returns 12754.089

My program will have up to 9 segments so this was just the beginning 3 Segments.
Not sure it is going to work Unless I missed something in the formula.

 

[afzaldir]

Why are you trying in VB. May better possible in Excel.

 

[WineSnob]

It is part of an Access application where the user can input the value and have it return the monthly amount. Is it possible to create the string in access then send it to excel and the have the result go back into access all invisible to the user? I have never done anything like that.

 

[WineSnob]

I just thought that is what I am sort of doing now. If I could get it to work in excel I could get to work in Access. I am at a lost. Not sure what I want is possible in Access.

 

[CJ_London]

It is perfectly possible to do in VBA

I don't think it is easy to send the string to excel since the method of laying out the calculation is different - e.g. x*y^2 in vba would be =x*power(y,2) in excel.

Going back to the formula, I've got a bit more time this evening and have solved it (basically, repositioning of some brackets). I created a table called table3 with a field called X and populated it with 8000.

If you do the same and copy the following query into it you will see the result of each step

SELECT ([x]*(1-(1+(0.0008333))^-48)/(0.0008333))+((([x]*60)*(1+0.04)^-4)/60)*(1-(1+0.0008333)^-60)/0.0008333+((([x]*84)*(1+0.04)^-9)/84)*(1-(1+0.0008333)^-84)/0.0008333 AS Original, 
[x]*((1-(1+0.0008333)^-48)/0.0008333)+[x]*(((60*(1+0.04)^-4)/60)*(1-(1+0.0008333)^-60)/0.0008333)+[x]*(((84*(1+0.04)^-9)/84)*(1-(1+0.0008333)^-84)/0.0008333) AS SimplifyBrackets, 
[x]*(((1-(1+0.0008333)^-48)/0.0008333)+(((60*(1+0.04)^-4)/60)*(1-(1+0.0008333)^-60)/0.0008333)+(((84*(1+0.04)^-9)/84)*(1-(1+0.0008333)^-84)/0.0008333)) AS Combining, 
1232132.81/(((1-(1+0.0008333)^-48)/0.0008333)+(((60*(1+0.04)^-4)/60)*(1-(1+0.0008333)^-60)/0.0008333)+(((84*(1+0.04)^-9)/84)*(1-(1+0.0008333)^-84)/0.0008333)) AS ValueX
FROM Table3

My program will have up to 9 segments so this was just the beginning 3 Segments

If you follow the same procedure which is has a logical process you should be able to add in the other 6 components

 

[WineSnob]

WOW! you really put some thought into this along with some wicked good math skills. This is AWESOME.... I may need to ask for a modification if I can't figure how to add a new component to the equation. I am going to give it go first before asking.
I cannot thank you enough ... I was ready to abandon the functionality.

 

[CJ_London]

I enjoy a challenge

 

[WineSnob]

Need a little more help with the additional component. I am too old to remember algebra. I appreciate your help.
If I needed to use x*(1+.02)^4 where x is AFTER the first + sign. How would I simplify brackets and combine?

SELECT ([x]*(1-(1+(0.0008333))^-48)/(0.0008333))+((([x]*60)*(1+0.04)^-4)/60)*(1-(1+0.0008333)^-60)/0.0008333+((([x]*84)*(1+0.04)^-9)/84)*(1-(1+0.0008333)^-84)/0.0008333 AS Original,
[x]*((1-(1+0.0008333)^-48)/0.0008333)+[x]*(((60*(1+0.04)^-4)/60)*(1-(1+0.0008333)^-60)/0.0008333)+[x]*(((84*(1+0.04)^-9)/84)*(1-(1+0.0008333)^-84)/0.0008333) AS SimplifyBrackets,
[x]*(((1-(1+0.0008333)^-48)/0.0008333)+(((60*(1+0.04)^-4)/60)*(1-(1+0.0008333)^-60)/0.0008333)+(((84*(1+0.04)^-9)/84)*(1-(1+0.0008333)^-84)/0.0008333)) AS Combining,
1232132.81/(((1-(1+0.0008333)^-48)/0.0008333)+(((60*(1+0.04)^-4)/60)*(1-(1+0.0008333)^-60)/0.0008333)+(((84*(1+0.04)^-9)/84)*(1-(1+0.0008333)^-84)/0.0008333)) AS ValueX
FROM Table3

The first x will always be x. After that then x could be x*(1+.02)^4 example
x = 3000
x*(1+.02)^4 = 3247.29 or

x may equal x throughout as it is now.
This is a variable
x is monthly amount = 3000
here = x*(1+.02)^4 = 3247.29
where .02 inflation rate
and ^4 is the deferral time frame

THANKS AGAIN

 

[CJ_London]

The objective of the exercise is to simplify the equation and move the X to the outside of the equation. Your equation has a number of superfluous brackets which need to be removed and also can be bracketed differently to produce the same answer

[SIZE=3][COLOR=red][B]([/B][/COLOR][/SIZE][x]*(1-(1+(0.0008333))^-48)/[SIZE=3][COLOR=seagreen][B]([/B][/COLOR][/SIZE]0.0008333[SIZE=3][B][COLOR=seagreen])[/COLOR][COLOR=red])[/COLOR][/B][/SIZE]
becomes
[x]*[SIZE=3][COLOR=orange][B]([/B][/COLOR][/SIZE](1-(1+0.0008333)^-48)/0.0008333[SIZE=3][COLOR=orange][B])[/B][/COLOR][/SIZE]

By inspection as a process

• The outer brackets are not required (in red) so both can be removed
• then run the query to check you have the same answer
• The brackets around the second 0.0008333 (in green) are not required so can be removed
• then run the query to check you have the same answer
• the equation could be left as is, but we want to ringfence the calculation to be multiplied by X so we surround with brackets (in Orange)
• then run the query to check you have the same answer
• move on to the next component

This also looks like the X has simply been moved outside the brackets, but this is not the case in the second component where there is no outer bracket (red) - no bracket at the end on the original view. If you go through the same steps as above it will work - no outer brakcets to remove (red), remove the pair in green, add the pair in orange, move the X outside note: in this one there are no out

(([SIZE=3][COLOR=seagreen][B]([/B][/COLOR][/SIZE][x]*60[SIZE=3][COLOR=seagreen][B])[/B][/COLOR][/SIZE]*(1+0.04)^-4)/60)*(1-(1+0.0008333)^-60)/0.0008333
[B][SIZE=3][COLOR=orange]([/COLOR][/SIZE][/B]((60*(1+0.04)^-4)/60)*(1-(1+0.0008333)^-60)/0.0008333[B][SIZE=3][COLOR=orange])[/COLOR][/SIZE][/B]

The simple rule is you need to keep brackets around elements of the equation which involve + or - but can remove them where it is multiply or divide - so these stay (1-(1+(0.0008333)).

Go through each element, removing pairs of brackets and checking as you go