Calculating a forecast for my investment account

[sweetbobby]

I have a column of numbers where I enter the daily account balance for my investment account.

Let's assume that the highest account value in my string of numbers is $100,000.

If the LAST number that I enter in the column (ie today's balance) is greater than 5% lower than the LARGEST number in my column, this should report as a "ARMAGEDDON" (which tells me it's time to liquidate my positions).
(Account balance is > 5% lower than the highest number. So a drop in the account balance of anything greater than $5,000.)

If the LAST number entered is 5% lower than the LARGEST number in the column, this should report as "HURRICANE".
(Account balance is > 4% to 5% lower than the highest number. So a drop in the account balance of $4,001 to $5,000.)

If the LAST number entered is 4% lower than the LARGEST number in the column, this should report as "STORMY".
(Account balance is > 3% to 4% lower than the highest number. So a drop in the account balance of $3,001 to $4,000.)

If the LAST number entered is 3% lower than the LARGEST number in the column, this should report as "RAINY".
(Account balance is > 2% to 3% lower than the highest number. So a drop in the account balance of $2,001 to $3,000.)

If the LAST number entered is 2% lower than the LARGEST number in the column, this should report as "CLOUDY".
(Account balance is > 1% to 2% lower than the highest number. So a drop in the account balance of $1,001 to $2,000.)

If the LAST number entered is 1% lower than the LARGEST number in the column, this should report as "SUNNY". (Account balance is 0 to 1% lower than the highest number. So a drop in the account balance of $0 to $1,000.

I would also like to do this same analysis that gives that does this same forecast from the previous day's account balance (the next to the last number in my column). So, if I enter my account balance today and it is represents a greater than 5% drop from the previous day's balance, it would report Armageddon.

I hope this makes sense and I appreciate your help!

Bobby

 

[sdruley]

One approach to consider here is to take your column of account balance data and make sure that the observations you make about the current balance are equally spaced, like once a week or once a month. Let's say the last 10 observations are 10, $7000; 11, $6500; 12, $7700; 13, $8900; 14, $8700; 15, $8996; 16 $9002; 17, $9030: 18, $9101; 19, $8900; Use the Forcast.ETS function to make predictions about what the balance will be for the next interval. Make sure to use all of the data you have available. Now the 11th data point is 20F; $8,800. There are more accurate and complicated ways to do this but in the interest of simplicity...Use PERCENTRANK.INC to evaluate the forecasted point to the last 10 points. A rank of 48% or 52% means "SUNNY". A rank of 45% or 55% means "CLOUDY". A rank of 41% or 59% means "RAINY". A rank of 39% or 61% means "STORMY" and a rank of less than 39% or more than 61% means "HURRICANE". Although this is not perfect, it is a practical approach that puts some statistical meaning into your classifications.