• Welcome to OGBoards 10.0, keep in mind that we will be making LOTS of changes to smooth out the experience here and make it as close as possible functionally to the old software, but feel free to drop suggestions or requests in the Tech Support subforum!

The Excel Help Thread

no, $61,300 is correct (or close enough) for that specific scenario

but I want a formula where I don't have to trial-and-error my way to the answer each time I change my assumptions for term length, value of total payments, etc.
 
Biff's number is right. It's not an excel problem, it's a math problem. You're wanting to solve for P ("principal") where the sum of P*(1.03)^k from k=0 to k=59 is equal to $10,000,000. That sum formula is expressed as P*(1-1.03^60)/(1-1.03) and solving backwards for P gives you $61,329.59.

If you wanted to make an excel formula with the following variables:
A1=payout sum=$10,000,000
A2=number of years=60
A3=inflation rate=3%

=A1*(1-(1+A3))/(1-(1+A3)^A2)
 
Hey excel wizards. Have a question.

I have a spreadsheet that I'd like to copy down the column and I want one of the variables to change (the way it usually does) but I want another variable to stay the same. Is that possible?

Here is the formula:

=(2144.63+(40*F2))/SUM(B2:C25)

I want the F2 to change to F3, F4 etc. as I copy it down the column, but I want the denominator to stay the same as it copies down (i.e. I want all the copies formulas to still have SUM(B2:C25))
 
Hey excel wizards. Have a question.

I have a spreadsheet that I'd like to copy down the column and I want one of the variables to change (the way it usually does) but I want another variable to stay the same. Is that possible?

Here is the formula:

=(2144.63+(40*F2))/SUM(B2:C25)

I want the F2 to change to F3, F4 etc. as I copy it down the column, but I want the denominator to stay the same as it copies down (i.e. I want all the copies formulas to still have SUM(B2:C25))

replace B2:C25 with $B$2:$C$25

First $ "anchors" column B, second $ anchors row 2

if you open the formula and highlight B2:C25, hit F4, it will lock that in - then toggle to row anchored only, column anchored only, nothing anchored
 
Last edited:
replace B2:C25 with $B$2:$C$25

First $ "anchors" column B, second $ anchors row 2

if you open the formula and highlight B2:C25, hit F4, it will lock that in - then toggle to row anchored only, column anchored only, nothing anchored

Awesome, thank you!
 
Back
Top