Rolling Average Formula

E

EvilBankster

New Member
Joined
Jun 6, 2011
Messages
6
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
What version of Excel do you have?

Are you asking to average the last 12 entries in a column greater than zero?
 
Upvote 0
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).
 
Upvote 0
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?
 
Upvote 0
barry houdini said:
Hello Evil Bankster, when are we getting our money back? :)
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calculate principal amount on daily interest
Replies
5
Views
842
aurelius33
A
A
  • Question Question
Loan Tracker with prior/missed payments
Replies
0
Views
99
aurelius33
A
J
  • Question Question
Actual loan rate with fees calculation
Replies
6
Views
574
Dave Patton
D
G
  • Question Question
Get Opening Balance from Spilled Range
Replies
3
Views
548
gi0rg0s
G
A
  • Question Question
Calculate Optimum Duration in Loan
Replies
1
Views
634
anuradhagrewal
A

Forum statistics

Threads
1,224,612
Messages
6,179,890
Members
452,948
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top