Proposed Project for the MSU Industrial Math Students

Improving Understanding of Average Daily Balance Method Over a Large Dataset

This project will involve a review of the Average Daily Balance calculations that the Municipal Employees’ Retirement System (MERS) uses when allocating out quarterly investment gains and losses to the 2,000+ municipal divisions that make up MERS. MERS runs a Defined Benefit Pension Plan for the municipalities in the State of Michigan. On a quarterly basis MERS allocates the investment gains or losses for the quarter to the individual municipal divisions based on the average daily balance (ADB) of assets of that municipal division. The average daily balance fluctuates based on contributions coming into the divisions from the municipalities and pensions going out to retirees from the divisions.

The project’s goal is for the students to replicate the amount calculated for each of the 2,000+ divisions compared with MERS software calculations. This project had been run a few years ago and the results were what we expected, and the project had been run last year. Since then however we have had new software installed for the ADB calculation so re-running this project is desirable. The software now has an administrative expense allocation piece that is similar to the interest allocation only for specific organizational expenses. While this is an extra component to the process the calculations and allocations remain the same as the interest allocation.

The Industrial Mathematics Student Team at Michigan State University will receive from MERS an Excel file containing the beginning asset numbers and all the financial activity for the quarters/year. The students will also be given a dollar amount to allocate out for each of the four quarters in the year. The data from MERS will not have municipal names or Social Security numbers. The calculations themselves will probably not require any really complicated software, but an understanding of how the process and design works will be critical. The database will be very large with probably over 400,000 lines of information. One concern is that the data will not be available for the students until mid-February. I know you want to challenge the students and I am hoping this will give them a real world project that they can see the results of and that will also help our organization. We look forward to working with you.

Top of Page