Financial MARKET FORMULAS TEST 1

What is a daily return?

How much the price changed in one day (as a %).

Excel for daily return (simple)?

Daily return formula (simple)?

= (PriceToday / PriceYesterday)-1

What is average daily return?

The mean of daily returns.
AVERAGE(StockRet)

Why multiply by 252?

~252 trading days → daily to annual.

Excel annualized return (Named Range)?

Back: =AVERAGE(StockRet)*252

Profit per share_i or profit per share for stock

​ = Expected_i ​− Current_i​

When prices differ for the different stocks then its best to use

profit per dollar invested (ROI):

profit per dollar invested (ROI) or ROI_i:

(future price - current price) / current price

To maximize profit with only a budget constraint, the optimal move is:

Put as much money as possible into the stock with the highest ROI.

What am I actually choosing in an optimization portfolio problem?

The position size for each asset: usually Sharesᵢ (or weights wᵢ).

Why it matters: If you don’t clearly define what you control, Solver has nothing real to optimize.

Position size

= “How big is my bet on this thing?”

• In this assignment, position size is: Sharesᵢ = how many shares of stock i you buy.

• In other versions, position size might be: Weightᵢ (wᵢ) = what % of your money goes into stock

Decision Variable:

the cells Solver is allowed to change (e.g., Sharesᵢ).

Objective (Target):

the thing you’re trying to maximize/minimize (e.g., Total Profit).

Constraint:

a rule Solver must obey (e.g., Total Cost ≤ Budget).

Why calculate “Costᵢ”?

To translate “shares” into dollars spent.

Formula: Costᵢ

= PriceTodayᵢ × Sharesᵢ

What is knowing the cost used for:

Budget rules: “Don’t spend more than $X.”
If you skip it: You can’t enforce the budget constraint correctly.

Why would I want Total Cost?

Because budgets are portfolio-level: you spend money on all assets combined.

Formula: TotalCost

= Σ Costᵢ
Σ = SUM

What is Total Cost used for?:

The main constraint: TotalCost ≤ Budget
Reality check: If TotalCost is way below budget, you’re leaving money on the table (unless that’s allowed/optimal).

Why calculate future value if I’m maximizing profit?

Because profit is “future minus today.” You need the “future” side of that subtraction.

Formula: FutureValueᵢ

= ExpectedPriceᵢ × Sharesᵢ

What is FutureValueᵢ used for?

Building profit cleanly and auditing results.

Why calculate Profitᵢ instead of just eyeballing return?

A: Solver needs a numeric objective. Profit is the cleanest objective when your goal is dollars gained.

Formula Profit (two equivalent ways):

• Profitᵢ = FutureValueᵢ − Costᵢ

• Profitᵢ = (ExpectedPriceᵢ − PriceTodayᵢ) × Sharesᵢ
  What it’s used for: Summing into the objective.

Profitᵢ

= FutureValueᵢ − Costᵢ

Profitᵢ

= (ExpectedPriceᵢ − PriceTodayᵢ) × Sharesᵢ

Why would I want Total Profit?

A: That’s the thing you’re trying to maximize: “How much money do I make?”

Formula: TotalProfit

= Σ Profitᵢ
What it’s used for: The Solver objective (maximize it).

Why compute ROIᵢ if Solver already maximizes profit?

A: ROI is your human cheat code for understanding what Solver will prefer.

What it’s used for:

• sanity-checking results (Solver should favor high ROI)

• quickly spotting the “best bang per buck” asset
  Important: ROI helps interpretation; it’s usually not required for Solver.

Formula: ROIᵢ

= (ExpectedPriceᵢ − PriceTodayᵢ) / PriceTodayᵢ

Highest ROI value in the ROI column

If your ROI values are in a range (say “ROI column” rows for all stocks):

• Highest ROI:
  =MAX(ROI_range)

Which row/stock has that highest ROI

• Row position within the ROI range:
  =MATCH(MAX(ROI_range), ROI_range, 0)

If you want the actual Stock # (from your Stock column)

• Stock # returned:
  =INDEX(Stock_range, MATCH(MAX(ROI_range), ROI_range, 0))

That combo tells you both the max ROI and which stock achieved it.

Why track leftover cash?

A: Because real constraints (like whole shares) can prevent perfect spending.

What it’s used for:

• checking if you used the budget efficiently

• explaining why Solver might buy a “second asset” to use leftover cash better

Formula: CashLeft

= Budget − TotalCost

Why require Shares ≥ 0? Why force shares to be nonnegative?

A: To ban short-selling. Negative shares = you’re betting against the stock.
Constraint: Sharesᵢ ≥ 0
What it’s used for: Keeps the problem aligned with “buy-only” portfolios.

Why require Shares to be integers?

Q: Why make shares integers?

A: Because you usually can’t buy fractions of a share (unless fractional trading is allowed).
Constraint: Sharesᵢ is integer
What it’s used for: Makes the solution realistic, but creates leftover cash issues.

What are the only three things Solver really cares about?

A:

1. Objective (what to maximize/minimize): e.g., TotalProfit

2. Decision variables (what it can change): Sharesᵢ

3. Constraints (rules): budget, nonnegativity, integers, risk limits, etc.
   Why it matters: If any one is wrong, Solver gives nonsense or “nothing changes.”

How do I know my sheet isn’t lying to me before I optimize?

A: Set one Sharesᵢ = 1 and verify:

• Costᵢ becomes PriceTodayᵢ

• Profitᵢ becomes Expected−Current

• Totals change
  Why it matters: If totals don’t change, Solver can’t move anything meaningfully.

the whole idea of worst-case scenario management:

you’re maximizing upside, but you put a hard floor under how bad things are allowed to get.

Set up Solver it so it can’t buy more than 100

Interval less than or equal to 100

Set up Solver it so it can’t go under zero

interval greater than or equal 0

Solver can’t be zero or less than zero

interval greater than or equal 0

Interval is a intiger

What will you need to make sure you have ready every time you do one of these problems?