Rolling Average Formula

[EvilBankster]

Hello all,

I am a long-time reader and user of the tips and tricks this forum has provided but am just now a first-time poster. I've finally encountered an excel problem that I can't figure out how to work and don't believe (please correct me if I'm wrong) that my problem has been addressed specifically.

Background: I am one of these evil bankers you hear about in the news and have a very large Excel worksheet that I use for various calculations. One of these tools is for pricing interest rate swaps. Sparing you all the various details of this mechanism, here's the excel thing I can't figure out:

1. 1. I have a column of data that represents the actual amortization for a given loan (PPMT formula).
2. This amount is dynamic but when we construct a swap we keep the principal payment fixed for 12 month periods.
3. I need to construct a formula that will take the average principal payment over any given 12 month period (currently using a MOD formula to do this).
4. The kicker: it needs to be dynamic such that, if there is an interest-only period for the loan, it does not account for those months of $0 in principal payment. My MOD formula blows up at this and I'm at wits end.

That probably sounds relatively simple, but I assure you there are a lot of moving parts to the rest of this spreadsheet that will (more than likely) mean I'll have to come back several times to you experts. Another rule: I cannot use VBA to complete the formula.

I am hesitant to share any code or the spreadsheet itself (proprietary stuff in some parts), but if this would help, let me know exactly what you'd need to see and I can re-create the formulas in a simple spreadsheet to highlight the mechanics of it all.

Thank you in advance. Every Excel question I've had to date invariably involves a trip to this forum.

 

[AlphaFrog]

What version of Excel do you have?

Are you asking to average the last 12 entries in a column greater than zero?

 

[EvilBankster]

My apologies, I forgot to put version information. I'm running Excel 2003 and Windows XP.

I'm looking to take an average of every 12 entries in a column greater than zero. The number of zero entries (representing no principal amortization) will vary based on the input in another cell (months of interest-only financing).

 

[barry houdini]

Hello Evil Bankster, when are we getting our money back?

If you have data in A2 down try this formula in B13

=AVERAGE(IF(A2:A13>0,A2:A13))

confirmed with CTRL+SHIFT+ENTER and copied down

....or non-array version

=SUM(A2:A13)/COUNTIF(A2:A13,">0")

Does that do it?

 

[EvilBankster]

Hello Evil Bankster, when are we getting our money back?

What money?


But seriously, hopefully this will help paint a better picture. Since I can't figure out how to post a workbook or do something to actually show an example workbook, try and follow this:

Variables:
Amortization Term (in months): C5
Interest Rate: C6
Interest Only Period (in months): C7
Amount: C8

Column A: Payment number (listed as 1 through X)
Column B: Actual amortization schedule with formula of
=IF(A1<=C$7,0,-PPMT(C$6,A15,C$5,C$8,0,0))

Column D: Principal balance with formula of:
=IF(B15=0,C$8,C$8-C15)

Column C: This needs to be the "average" amortization line where the average of B1:B13 (if no period of interest only) is displayed for cells C1:C13. Cells C14:C25 should show the AVERAGE(B14:B25). And so on.

If there is a 6 month period of interest only then it should display the average of B7:B18 in cells C7:C18 but cells C1:C6 should show $0. And the AVERAGE(B19:B30) should display in cells C19:C30.

Does that help? Currently I have a ROW/MOD formula that displays the average of column B in every 12th cell, but leaves the cells in between blank and does not allow for interest only periods.

 

[EvilBankster]

Am I allowed to BTT?