Harlan - or anybody good at math

[Guest]

Is there a way to streamline this calculation??

From a cohort's spreadsheet:

Given a series of varying interest rates by month

AR = annualized rate each period
MR = AR/12
CF = monthly cash flow

Col1........................................Col2
CF1/(1+MR1)............................CF1*AR1/(1+MR1)
CF2/(1+MR1)(1+MR2)...............CF2*AR2/(1+MR1)(1+MR2)
etc

Desired answer is: Sum(Col2)/sum(Col1)

If AR is fixed for all months, this simply returns the static rate.

The denominator in each month is identical for both columns, as is the CF
component in the numerator. It sure seems to me that there ought to be a
way to simplify the calculations in the event the rates vary by month. Is
there?

Thanks

 

[mdschuetz]

Hi there,


Maybe I just don't understand your original formula. Why do you add 1+MR? I don't understand the logic behind that.

I've attached a quick workup, take a look at it and tell me if this is what you're trying to accomplish.