Inflation - Deflation Calculator

I am working on a calculator that could automatically adjust the balance of two asset classes. One asset class is a Deflationary protection. It is CASH sitting in a very secure account with guarantees on growth rates. If Deflation kicks in, and I want to arrive at the BUYING POWER of a dollar that is also earning interest at a given rate, then it's simple to calculate the fall in prices, vs the rise in the account to get the effectual buying power.

In this spreadsheet i loosely label GOLD as the inflationary asset class.

See the attached spreadsheet. The TAB 1 - Deflation is easy.

SHEET two is a real problem.

Let's say that I have $100,000 to invest. And my CASH guarantee is 7.2% (this is from current annuity guarantee levels, so don't fret about that. You can input anything here, but make sure to see where I have already arranged to procure data for cells, because SOME of the top bracket on Sheet 2 comes from sheet 1). And let's say that inflation is 9%. So I have to make up 2.8% somewhere else. I went through a lot of ways to try to approach the problem. I tried to find a ratio of the base amount of cash vs. an inflation earning gold asset, that would adjust and close an equation wherein perhaps the ratio of assets would be relative to the projected rate of inflation (See yellow cell on Sheet 2) but the bottom line is that once inflation becomes higher than the base interest rate of my cash, nothing adjusts for it except 100% push over to gold.

You'll see another approach on the bottom where I thought I could add to the existing cash just enough of any asset that would earn the spread between the cash guaranteed at 7.2% and the inputted inflation rate I could get an amount need to be invested in gold. And yes, that works and adjusts, but it is still over and above the base principal I brought to the table to start the whole thing.

What I would ideally like to solve is this: I can give you the guaranteed rate that my cash will earn. Say 7.2%. But if I want to also determine how much I should take away from the cash and shift to gold growing at the rate of inflation, I should be able to find a result that will tell me either:

1. Given a presumed rate of inflation, how should I balance my portfolio between the cash base and gold from a basic principal amount?
2. Given an inflation rate, how much EXTRA should I invest in gold to keep my value of total funds from simply falling behind the inflation rate? (This could get weird, though, if not put back into a calculated RATIO.)

I suspect that Goal Seeker may come in handy for this, but I have not figured out how to plug it in to this equation.

THANKS TONS for any help!!!

Bruce
[email protected]