Re: Help with Equation
Hi Vitali,
I'm not certain I entirely understand your ask, but I'll take a shot using your example
Where:
Current Cash Value = $1,179.80
Current Cash Reserve Percent = 59%
Desired Cash Reserve Percent = 43.5%
Current Share Price = $13.08
Step 1: Convert Current Cash Value to 100% Cash Value
100% Cash Value = Current Cash Value / Current Cash Reserve Percent
Ex: $1,999.66 = $1,179.80 / 59%
Step 2: Calculate Cash Reserve Delta Percent
Cash Reserve Delta Percent = Current Cash Reserve Percent - Desired Cash Reserve Percent
Ex: 15.5% = 59% - 43.5%
Step 3: Calculate Dollar Amount to Sell
Dollar Amount to Sell = 100% Cash Value * Cash Reserve Delta Percent
Ex: $309.95 = $1,999.66 * 15.5%
Step 4: Calculate Number of Shares to Sell
Number of Shares to Sell = Dollar Amount to Sell / Current Share Price
Ex: 23.7 = $309.95 / $13.08
Proof:
Desired Cash Reserve Percent = (Current Cash Value - Dollar Amount to Sell) / 100% Cash Value
Ex: 43.5% = ($1,179.80 - $309.95) / $1,999.66
Note: New Cash Value = $869.85 = ($1,179.80 - $309.95)
This example will only work when Desired Cash Reserve Percent < Current Cash Reserve Percent, but can be easily modified.
It will be difficult to achieve this in a single Excel equation without computing one or more of the intermediate values noted above (or without writing a macro.)
Let me know if this is what you're looking for. Otherwise, I'll make another attempt.
-AIMStudent
