quote:
Sheets Real Options Model

Certainly! I'd be happy to help you create a real options model in Google Sheets. Real options models can be used to evaluate investment opportunities that have uncertain outcomes, such as projects with long-term payoffs, investments in research and development, or investments in new technology.

To create a real options model in Google Sheets, you will need to use some financial formulas and functions. Here's an overview of the steps involved:

Set up your model inputs: Start by identifying the key inputs for your model, such as the initial investment cost, the expected cash flows, the discount rate, and the volatility of the cash flows. You can enter these inputs in a separate section of your sheet.

Calculate the net present value (NPV) of the expected cash flows: Use the NPV formula to calculate the present value of the expected cash flows, discounted at the appropriate rate. This will give you an estimate of the expected value of the investment.

Determine the option value of the investment: Use an option pricing model, such as the Black-Scholes model, to estimate the option value of the investment. This will depend on the volatility of the cash flows and the time horizon of the investment.

Calculate the total value of the investment: Add the NPV and the option value together to get the total value of the investment.

Here's an example of how you can set up your real options model in Google Sheets:

Set up your model inputs:

Initial investment cost: $1,000,000
Expected cash flows: Year 1: $200,000, Year 2: $300,000, Year 3: $400,000, Year 4: $500,000, Year 5: $600,000
Discount rate: 10%
Volatility: 20%
Time horizon: 5 years
Calculate the net present value (NPV) of the expected cash flows:

In cell B2, enter "=NPV(B6,B3:B7)"
This formula calculates the NPV of the cash flows in cells B3:B7, using a discount rate of 10% in cell B6.
The result should be $1,337,476.12
Determine the option value of the investment:

In cell B9, enter "=NORM.S.DIST((-B6+B3)/B7,0,1,1)-B8EXP(-B6B4)NORM.S.DIST((-B6+B3)/(B7SQRT(B4)),0,1,1)"
This formula calculates the option value of the investment, using the Black-Scholes model.
The inputs are: current stock price (which is the NPV from step 2), strike price (which is the initial investment cost), time to expiration (which is the time horizon), risk-free rate (which is the discount rate), and volatility (which is the volatility of the cash flows).
The result should be $106,326.89
Calculate the total value of the investment:

In cell B11, enter "=B2+B9"
This formula adds the NPV from step 2 and the option value from step 3.
The result should be $1,443,803.01
This is a basic example, but you can modify the inputs and formulas to fit your specific needs. I hope this helps you get started with creating a real options model in Google Sheets!

Before ChatGPT, this would have taken me watching a few YouTube videos or trying to open my old MBA finance text books.

3 ...

Report Post

Posted by armsdealer

Member since Feb 2016

12290 posts

Posted on 4/3/23 at 9:34 pm to euphemus

Spreadsheet bots save my arse time all the time.

0 ...

Report Post

Posted by slackster

Houston

Member since Mar 2009

91491 posts

Posted on 4/3/23 at 9:35 pm to euphemus

quote:
Before ChatGPT, this would have taken me watching a few YouTube videos or trying to open my old MBA finance text books.

Yeah but you still have to know what to put where. They just gave you the formulas - you need a basic understanding of the actual concept to know how to use it.

Still pretty cool.

0 ...

Report Post

Posted by Displaced

Member since Dec 2011

33011 posts

Posted on 4/4/23 at 8:53 am to euphemus

Why did you have to validate it by calling it an expert first?

2 ...

Report Post

Posted by Diseasefreeforall

Member since Oct 2012

7294 posts

Posted on 4/4/23 at 9:51 am to Displaced

ChatGPT is awesome. It helped me out this morning by clarifying some stuff regarding a discharged loan in an 8k.

Long-term AI is probably going to destroy humanity's critical thinking skills though.

1 ...

Report Post

Posted by euphemus

Member since Mar 2014

560 posts

Posted on 4/4/23 at 12:22 pm to Displaced

quote:
Why did you have to validate it by calling it an expert first?

What I have learned in playing around with Bard and ChatGPT the past few days is that, sometimes the answers can vary based on how you prompt the AI. There are some deliberate phrases that when you use, it primes the AI the right way to give you the exact answer you are looking for.

In this particular case, it probably wasn't necessary as the question was so straight forward. But I have gotten into the habit of using the first sentence to set the right context for the generative AI.

Here's another example:

quote:
My new prompt:

You are a Google Sheets expert. Can you teach me how to create a real options model in Sheets assuming I am a 12 year old kid?

quote:
ChatGPT's response:

Sure, I'd be happy to help you create a real options model in Google Sheets.

But first, let me explain what a real options model is. A real options model is a tool used in financial analysis to evaluate investment opportunities that involve uncertainty. Unlike traditional financial models that assume a fixed set of cash flows, real options models account for the flexibility of management to change the course of an investment in response to changing market conditions.

Now, assuming you are a 12 year old kid, let's break down the steps of creating a simple real options model in Google Sheets.

Step 1: Create a new Google Sheet

Go to Google Drive and click on the "New" button, then select "Google Sheets" from the drop-down menu. This will create a new blank Google Sheet.

Step 2: Set up the input variables

In cell A1, type "Initial Investment". In cell B1, type "Cost of Capital". In cell C1, type "Project Value Today". In cell D1, type "Expected Growth Rate". In cell E1, type "Option Expiration Time". In cell F1, type "Volatility".

In cell A2, input the initial investment amount. In cell B2, input the cost of capital (i.e. the interest rate required to justify the investment). In cell C2, input the estimated project value today. In cell D2, input the expected growth rate of the project. In cell E2, input the option expiration time (i.e. the length of time during which the option can be exercised). In cell F2, input the volatility of the project's value.

Step 3: Create the present value calculation

In cell G1, type "Present Value". In cell G2, input the formula "=PV(B2,E2,C2)".

This formula calculates the present value of the estimated project value today, discounted at the cost of capital rate and for the duration of the option expiration time.

Step 4: Create the expected future value calculation

In cell H1, type "Expected Future Value". In cell H2, input the formula "=C2EXP(D2E2)".

This formula calculates the expected future value of the project at the end of the option expiration time, assuming the estimated project value today grows at the expected growth rate.

Step 5: Create the option value calculation

In cell I1, type "Option Value". In cell I2, input the formula "=MAX(H2-G2,0)".

This formula calculates the value of the option to invest in the project, which is the difference between the expected future value and the present value. If the option value is negative, it means the investment should not be made.

Step 6: Create a sensitivity analysis table

In cells K1 to K6, type the following labels: "Initial Investment", "Cost of Capital", "Project Value Today", "Expected Growth Rate", "Option Expiration Time", and "Volatility".

In cells L1 to L6, input a range of values for each input variable. For example, you can input values of $100, $200, $300, etc. for the initial investment, and values of 5%, 6%, 7%, etc. for the cost of capital.

In cells M2 to M7, input the following formula: "=PV($B$2,M$1,$C$2)" for the present value calculation, "=C$2EXP(M$1$D$2)" for the expected future value calculation, and "=MAX(N$2-$M$2,0)" for the option value calculation.

Step 7: Create a chart to visualize the sensitivity analysis

Highlight cells K1 to M7

It's not perfect, but it will only get better from here on.

0 ...

Report Post

Posted by TheBoo

South to Louisiana

Member since Aug 2012

5456 posts

Posted on 4/4/23 at 12:44 pm to Diseasefreeforall

quote:
Long-term AI is probably going to destroy humanity's critical thinking skills though.

THIS. having the world's information in your pocket has already wrecked the worth ethic and problem solving skills of a generation. This will only exacerbate the issue.

Page 1