D
Duke Carey
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
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