Crafty: the blog

by John Rose

Create a 30-year Reserves Study using Excel

A reserves study looks ahead 30 years. It lists reserves items (components) and details the cost and schedule for maintenance, repair and replacement of each.

I embarked on this project to mirror and to be able to edit a professionally done reserves study for my Homeowners Association (HOA)—a formal name for a condomoinium governing structure. I want to review that study and be able to make corrections and vary payment scenarios to inform future versions of the same study.

That professional study was, I believe, created with Reserve Study HOA software, which can be purchased. It could be a time saver, but my interest is to fully understand the formulas and connections that make up a study without the expense—thus this creation of an Excel version.

The pages of our workbook

The array of workbook pages as they appear in Excel at the bottom of each spreadsheet

A horizontal list of the workbook pages
In Excel, we navigate from one page to the other at the workbook list of pages

We create an Excel workbook with pages (worksheets) as shown above. These pages list the reserves components in detail and different pages have costs calculated in various ways.

Each worksheet will be explained as we go along. You could set up those worksheets now.

I have put together an abbreviated model study that will work if you have Excel. I added a couple of dummy items to show results on the various pages. The last page in the workbook has all the formulas and instructions for where to use them. Click below to download the workbook. You can also open the formulas page, which is also included in the workbook.

The Cash Flow page (spreadsheet)

The key details of a reserves study are summed up on the Cash Flow page, where we can see at a glance the degree to which anticipated costs are covered by expected income. It shows, year-by-year, the money coming in and going out for the maintenance, repair and replacement of items serviced on a regular basis over the upcoming thirty years.

The Cash Flow page has items entered into the columns. Some columns are total amounts pulled in from other pages (relative references), and some are calculations made with elements of the Cash Flow page.

Columns B, E and G—Current Cost, Annual Expenditure and Fully Funded—are totals pulled in from other spreadsheets in the workbook.

Since the Cash Flow page is the great summing-up of all the other information we have yet to establish on other spreadsheets within the workbook, we will return to Cash Flow toward the end of this article once we understand the elements that feed into it.

The Cash Flow page- all columns and the first few rows (years)

A portion of the spreadsheet
  • This study example begins at year 2024, but there are a couple of items from 2023 that are refered to in the page's formulas, so those amounts are listed.
  • Current Cost entries are totals that will be pulled in from the Current Cost page. The Current Cost of a component is what servicing that item would cost in the current year, even though the item may not be scheduled for many years to come.
  • Annual Contribution is an amount set by the HOA board. It is the main income into the reserves account.
  • Annual Interest is calculated from the amount in the reserves account and is a small part of the yearly income into the reservces account.
  • Project Ending Reserves is the total in the reserves account after all yearly income is reported and the expenses are paid.
  • Fully Funded amounts are yearly totals pulled in from the Fully Funded page. It is the cumulative amount to date that would be in the account were all the items fully paid to date, with the understanding that equal amounts, with inflation costs added, will continue to be contributed before the item is actually serviced. I find this the trickiest item to understand.
  • Percent Funded is very useful for understanding the readiness of the HOA to pay for the reserves items. A figure right around 60% - 70% is cited as being good. Higher levels of funding are even better but rare in the real world.
  • Contribution % Change is not always on a cash flow page. It is useful for tracking year-by-year changes to the HOA dues—the Annual Contribution—and can help in creating scenarios for future payments.

The Parameters Worksheet (Page)

Parameters

Set the inflation rate, taxable rate and interest rate and some totals from the previous year
This study, beginning in 2024, needs a few totals from 2023 for formulas on the Cash Flow page

A first page to set up is Parameters, a "source of truth" page where we enter amounts that will be used in formulas.

The Inflation Rate is used in many formulas, and the Interest Rate and Taxable Rate are used in formulas on the Cash Flow page.

Set year today for planning

Many of the formulas rely on subtracting one year from another, and one of those years is often the "current year," which should be the year of the study—in our case 2024. But the reserves study is a planning document, and so is often put together in the year before the start of the study.

To make the study accurate for year 1 of the study, the year in which it is planned to begin, I use the year entered in "Set year today for planning" as a relative reference.

If you're familiar with the Excel YEAR(TODAY()) function, this would work once the actual year is 2024, but for planning that would read as, in this example, 2023, and our formulas would be off by one year.

Those first four items, Inflation Rate, Interest Rate, Taxable Rate, and Current Planning Year are the key ones for the Parameters page. I add the amounts in the "Study" part of the page as a reference for some key calculations on the Cash Flow page. These are amounts from the year previous to the study.

The Original Components Page

Original Components is, like Parameters, a "source of truth" page. Each component of the study is listed along with its attributes. These are the key amounts that are referenced throughout the workbook. A change here will ripple through the workbook results.

Shown below are all the columns and some of the rows of the Original Components page. The page will be whatever length is needed to list all the components.

This list is sorted first by replacement year; then, within any given year, the component names are listed alphabetically.

The Original Components spreadsheet listing components and their key attributes

Reserves components are listed in rows and their details in columns
This is only a partial list of all the components. The components are listed and grouped first by the Replacement Year attribute, and within each year-group the components are listed alphabetically.

An ultimate goal of this page is to determine the Current Cost of each component.

Before looking at that cost calculation, let's focus on how the various time attributes are attached to each component.

Date in Service and Date in Service Cost

When a component is serviced, a record is kept of the year and the cost. This is recorded in columns B and C, Date in Service and Date in Service Cost.

We'll skip, for now, columns D and E to look at Useful Life, Adjustment, and Remaining Life.

Useful Life

The Useful Life of the component could be based on how long the component actually functioned, product information, a warranty (such as a roofing warranty), some requirement such as a required test of the item, or a manufacturer's or contractor's information or best guess,.

Adjustment

Adjustment is some change, either more years or fewer, to the Useful Life.

For example, a warranty on a roof may expire while it is still in good shape so it's replacement is adjusted to some number of years into the future. Maybe the board decides to move up the painting of the lobby. Perhaps a part doesn't last as long as expected (a negative adjustment).

Key time and cost attributes of components

The described items in spreadsheet format

Another example: Sometimes it is determined or discovered that an item has gone without being listed or serviced. That item will ge given a reasonable Useful Life, and the elapsed time when it was not listed or considered is added as an Adjustment.

Adjustments may be set to 0 or left blank once an item is serviced and we know the updated actual cost and useful life.

Calculating the Replacement Year (Column D)

The Replacement Year is when the item is scheduled to be serviced. We use a formula to do this:

Replacement Year = Date in Service + Useful Life + Adjustment

On our spreadsheet the first example of this is:

=B3 + F3 + G3

That formula is then dragged down the column to give the Replacement Year for each item.

Calculating the Remaining Life

We use the Replacement Year and the Planning Year from our Parameters page to calculate the Remaining Life

Remaining Life = Replacement Year - Planning Year

On the spreadsheet our first example is:

=$D3-Parameters!$C$5

That is dragged down the column to give all the results. The reference to the planning year is an absolute reference. Using the dollar signs mean that the formula will always use what is entered on that page in that column and that row in the formula. The first item will always refer to Column D but the row reference will change as the formula is dragged down the column.

Calculating Current Cost with Date In Service cost

With good records, we can almost always get our current cost from the information that has been presented.

What do we mean by Current Cost?

The current cost is the cost of any component were it to be done in a given year.

Current Cost = Date in Service Cost * (1 + Inflation Rate) ^ (Current Planning Year - Date in Service)

We have the Date in Service Cost in Column C. This amount is multiplied by 1 plus the Inflation Rate listed on the Parameters page. This is raised to the power of, from Parameters, the Current Planning Year minus the Date in Service. The caret ^ symbol in Excel means "to the power of."

The Excel formula for the first iteration of this—in our example column C and row 3—is:

=$C3 + (1 + Parameters!$C$2) ^ (Parameters!$C$5 - $B3)

The first few items in the Replacement Year column are due to be serviced in 2024 which, for our purposes at the time of this writing, is also the current planning year. When the next planning year rolls around, having serviced the item in the previous year, we should update that item's Date in Service to the year in which it was done and the Date in Service Cost to what was the actual cost that was paid. Having serviced that item, the cycle of that component's Useful Life begins anew.

Having covered this method of arriving at the Current Cost, I must emphasize that THIS IS NOT THE RECOMMENDED METHOD TO DO THIS. However, it is useful to set up an additional column for the page and run that formula as a check against the method we get into next.
The Current Cost can be derived from actual unit costs and take into consideration how many units there are and any other qualifier.

These attributes of the component are in other columns of the Original Components page.

Before leaving this section, we can note that there is another way to derive the "power of" number.

Insted of

^ (Parameters!$C$5 - $B3)

Current Planning Year minus the Date in Service year we get the same result with

(Useful Life + Adjustment)

which in the first iteration is:

=$C3 + (1 + Parameters!$C$2) ^ ($F3 + $G3)

This can be set up as a supplemental column as a check of the recommended formula.

Calculating Current Cost with Unit Cost and Cost Details

A more straightforward method to arrive at our current cost is to use the actual cost of a unit with consideration of how many units there are and any other qualifier that might affect the actual expenditure.

Here is another part of the Original Components page.

The first item in our example is "Asphalt Pavement - Clean and Seal."

This item has the Quantity of 3,660 square feet at a cost of 54 cents per square foot. This is the current square foot cost, not some historical amount. At least, that is the best guess. When the work is actually done, we will learn the true cost, but this is the cost we're using for planning our expense.

Rows J through M have the unit cost details used to derived the Current Cost

The columns and rows discussed in this section

The Original Components heading and the first item

The top part of the spreadsheet and the row with the very first item

Our formula for calculating current cost from the Date in Service cost involved the inflation rate. Working with Unit Cost, that should already be factored in, so the formula is straightforward.

Current Cost = Unit Cost * Quantity

and this first iteration at the Current Cost cell is:

=M3 * L3

We must also consider if there is a qualifier. There is an example on row 23 and is for "Concrete Pavement - Repair Allowance." It is calculated using the full cost of replacing all the concrete Pavement—all 3,380 square feet of it—but since this is just for some repair, it was decided that 6% of that total cost would cover a typical repair.

The formula for this is:

Current Cost = (Unit Cost * Quantity) * Qualifier

In this case, the item is on row 23 so that formula at thata row is:

=(M23 * L23) * J23

The quantity and the qualifier are fixed amounts. The Unit Cost is variable year to year and should change with inflation.

We want one formula that will work in every case. We could use the last formula given above, but I prefer to check if there is a qualifier and run the one formula, and if the qualifier cell is blank run the other. The following will work in the first Current Cost cell and can then be dragged down the column to make each calculation and it will work whether or not there is a qualifier.

=IF(ISNUMBER(J3), (K3*M3)*J3, K3*M3) 

The formula checks if there is a qualifier and, if there is one, runs the formula that takes it into account. Otherwise the formula that multiplies the unit cost times the number of units is used.

The unit cost, like all our expenses, is affected by inflation. When preparing the next year's reserves study, these unit costs should be increased to reflect the inflation rate.

The current year items and a couple of items for the subsequent year

Focus on the 2024 items

Focusing on the items to be done in the current year (in our example 2024), we see the previous Date in Service listed. I like having this information.

When an item is serviced in the current year, the Remaining Life is 0. The items serviced in the current year could just as well be listed with dates in service of the current year (example 2024) and with what we arrive at as the current cost in Date in Service Cost.

Let's investigate those two similar formulas.

Using the older Date in Service at the first component example the formula is:

=1772 * (1 + .037) ^ (2024 - 2021)

And the result will be $1,976 as the current cost. Were we to change the Date in Service item to reflect the year we are in (current planning year 2024) with the 2024 cost the formula will look like this:

=1976 * (1+ .037) ^ (2024 - 2024)

And the outcome of that is also 1976. Yes, an item to the power of 0 (2024 - 2024) is itself and not, as in multiplication, 0.

The formal reserves study for our HOA that I am reflecting here uses the latter method of listing, but I like seeing the timing of the previous iteration.

So...our method of arriving at Current Cost is to use the unit cost plus any quantity and qualifier.

One might add the alternate method in an additional column. The result should be the same, and if they are not, some troubleshooting can resolve the discrepancy.

With Current Cost calculated, we can add a sum of that column. We will see where this is useful when we create a spreadsheet that calculates all the current costs over the study's full 30 years.

Calculating First Future Cost

There is a column on our Original Components page that we haven't touched—First Future Cost.

First Future Cost is what any item is expected to cost when the year for servicing that item—the replacement year—rolls around.

We have caluclated the current cost using the unit cost and any quantity and qualifier. We use that Current Cost when calculating First Future Cost.

Calculate First Future Cost using Current Cost

The formula is based on this:

Current Cost * (1 + Interest Rate) ^ (Replacement Year - Current Planning Year)

The first instance is:

=$E3 * (1 + Parameters!$C$2) ^ ($D3 - Parameters!$C$5)

That can be dragged down the page to fill in for each component.

Before we're done, these items will have their own pages where we can see the costs of any item over the entire 30-year scope of the reserves study.

We can sum this column to compare with the results we get when we set up our 30-year look at costs.

Unfunded Components

Sometimes we want to keep a component listed but we don't want to count it in our totals. A few example items are shown below.

The first item is a scheme that has been formulated but not implemented by the board.

A couple of others are up for review and will probably be deemed unnecessary and ultimately removed from the list of components.

The Elevator Modernization is an item scheduled for way into the future. Typically, that would be funded on an ongoing basis. Because our building is engaged in a complete re-doing of our plumbing, quite an expensive process, payments for the future elevator mondernization are not being made. Once the plumbing is completed, this item will be funded and those payments will be made in a shorter period than 30 years.

By keeping the details of these items, they can be properly calculated into our totals should we wish to do so. By not entering an amount in First Future Cost we keep those costs from appearing in calculations that will include all the other components.

Cross-factoring the Original Components

I emphasized the calculation of Current Cost from the Unit Cost and details. Some cross-checking formulas may help troubleshoot inconsistencies.

I separate these by an empty column and add them to the Original Components page so that descrepancies are easy to spot.

Alternate formulas used to calculate the same component results

Some of the cross-check results on the Original Components page
The formula results along with checks that the alternate results match the other formula results on the same spreadsheet

Let's run through those in order.

Calculate Date in Service from Replacement Year.

We originally entered Date in Service as its own date based on records. That can also be a formula:

Date in Service Year = Replacement Year - Useful Life - Adjustment

and the first iteration is:

=$D3-$F3-$G3

Had this formula been used, we would get a circular reference error.

We could simply check the column entries against each other. Instead, in an adjoining column, we set up an IF() clause and use the Excel TRUE() function- here is the first iteration of that:

=IF($O3 = $B3, TRUE())

Dragging this down the column should give us all TRUE responses. Otherwise there is some troubleshooting to be done.

Calculate the Current Cost from the Date in Service Cost component life details

We calculated the Current Cost from the Unit Cost and any Quantity and Qualifier. Here we use life details—Useful Life, Adjustment, and Remaining Life—to calculate the current cost.

Current Cost = Date in Service Cost * (1 + Inflation Rate) ^ (Useful Life + Adjustment - Remaining Life)

And its first iteration is:

=$C3 * (1 + Parameters!$C$2)^ ($F3 + $G3 - $H3)

We set up a check of the results with:

=IF(Q3=E3, TRUE())

Calculate the First Future Cost using life details

By not subtracting the Remaining Life in the previous equation we calculate the First Future Cost.

=$C3 * (1 + Parameters!$C$2)^ ($F3 + $G3)

And we double-check that against the previously derived First Future Cost with:

=IF(I3=S3, TRUE())

Calculating the Unit Cost when we know only the Date in Service Cost

We entered a Unit Cost as a whole number and used that to calculate the cost of an item. It is useful to calculate that cost from other component attributes and to check our results.

The Unit Costs listed as whole numbers are the costs for the current year, even though the component may not be scheduled until future years.

If we don't have to factor a Quantity (the Quantity is 1) or a Qualifier (there is no qualifier) for our result, the Unit Cost will be the same as the Current Year cost.

Otherwise, we need to get any Quantity and Qualifier to come up with the Unit Cost. Those two factors will be fixed numbers, not variables, so once those are determined we can calculate the Unit Cost.

Calculate the Unit Cost with a Quantity and Qualifier from the Date in Service Cost

If there is a Quantity but no Qualifier, the calculation of the Unit Cost is:

Unit Cost = Date in Service Cost * (1 + Inflation Rate) ^ (Useful Life + Adjustment - Remaining Life) / Quantity
=$C3 * (1+Parameters!$C$2)^($F3 + $G3 - $H3) / $K3

And if there is also a Qualifier we divide by that Qualifier before we divide by the Quantity

(Date in Service Cost * (1 + Interest Rate) ^ (Useful Life + Adjustment - Remaining Life / Qualifier) / Quantity
=($C3 * (1+Parameters!$C$2)^($F3 + $G3 - $H3) /$J3) / $K3

But we want one formula that we can drag down the column and give correct results. For that we check if there is a qualifier and run one formula if there is, and if there is no qualifier we run the simpler one.

=IF(ISNUMBER($J3),($C3 * (1+Parameters!$C$2)^($F3 + $G3 - $H3) /$J3) / $K3,$C3 * (1+Parameters!$C$2)^($F3 + $G3 - $H3) / $K3)

This is an especially tricky number to arrive at, so it is beneficial to check if it matches up with the number entered in the Unit Cost column.

=IF($U3=$M3, TRUE())

This formula can show changes as inflation affects the unit cost, so it is beneficial to refer to teh results when the study is updated to another year.

This wraps up our work on the Original Components page; it's time to categorize our components.

Categorized Components

The Original Components page is sorted by the year when a given item is to be serviced. For easy reference and understanding, we want to have the same information organized by categories, and for this we create the Categorized Components spreadsheet.

This arrangement will serve as a template for other spreadsheets we will create.

Our column headings are the same as on the Original Components spreadsheet. Arranging the rows requires attention to detail.

Along with the information for each component, we want category headings, a row for category totals (though these aren't used in the example above), and some white space for separation.

The very first entry, Electrical System Maintenance, is on row 24 of the Original Components page. In the Description Cell we enter:

='Original Components'!A24

When we drag this across the row, we get all of the other amounts from that component entered into the respective cells as relative references; that is, the numbers on the Original Components page are reproduced on this page.

Recall that the Original Components page is a "source of truth" Making changes there will be reflected here.

Our second item, Electrical System Sub-panels Replace (1), is from line 76 of the Original Componets page, so our entry in Description for this component is:

='Original Components'!A76

Dragging that across the row will enter the details from the Original Components page for that item.

It is a bit painstaking to get this page set up. You might wonder why not set up this page with the items along with the formulas we used at Original Components. One could do that.

I mentioned that I am reflecting the work as it is published in our prefessionally done study. That study began with the information as it appears in the Original Components page, but did not provide this spreadsheet version of items in categories. Rather, they used a listing item by item in a series of plain text reports.

I also suggested that we might add our cross-reference formulas on the Original Components page. To put those here would make a bit of a mess of this page and move us away from the Original Components page as a source of truth.

It is meticulous work to get the Categorized Components set up, but it is more useful for reference and sets a method for looking at our components that will be used on the pages we will be setting up.

Side note about refering to other pages

When we refer to, say, inflation rate on the Parameters page, we start that formula with =Parameters!, and here when we refer to the Original Components page we begin 'Original Components'!. The subtle difference is the use of single quotes in the second example. This is to accommodate that the second example has a space in its name and to provide a correct reference. With no space in Parameters, the single quotes are not used.

Summing up the Categorized Components costs

The formulas that give us all the numbers on this page are referenced from the Original Components page.

We want to sum those totals on this page and also check those sums against the same sums on the Original Components spreadsheet.

As we can see in the image with the highlighted box and the formula in the formula bar, we have summed up the total for the Current Cost items with the formula =SUM(E2:E127).

Likewise, the First Future Costs are summed up with =SUM(I2:I127). These numbers should match the same totals on the Original Components page, so I refer to those below these sums and see that they match up. If they don't, we need to troubleshoot the problem.

If there is a discrepancy, a first step would be to see what the difference between the amounts is, then look for an item that matches that amount and see that it is being pulled into the Categorized Components page correctly. Also, check that each component on the Original Components page has a row listing on this, the Categorized Components spreadsheet.

Other than the references entered row-by-row, sums are the only formulas used in this spreadsheet. In the examples, I did not sum up each category, though I created a space to do so. I don't find this information particularly necessary, but if desired, there is no reason not to do it.

Were the categories to be summed up individually, we would then want to make sure that our totals for individual items are summed all together and do not include the category totals. It is a more complicated sum formula. Then the category totals can also be summed and a total derived for them. The two should match up, and that might be a useful check on the spreadsheet totals.

The most complicated setting-up of this workbook is done. We will use this organization by categories on several spreadsheets.

The Expenditures spreadsheet

The Expenditures page answers the question "How much will we spend each year on reserves items?"

This sum of each year is entered on the Cash Flow page and is an important figure when calculating the amount left in the reserves in any given year.

A section of the expenditures page

Row and column headings with some expenditures items and amounts

Calculate expenditures in the current year

We use the same organization of items in categories as on the Categorized Categories page.

In cell A2 we write:

='Categorized Components'!A2

And then drag that down the first column to fill in all the Categorized Components listings.

When we set up the Categorized Components spreadsheet, we had to carefully arrange which row was refering to which row on the Original Components spreadsheet. For this page, we are refering to the Categorized Components page, so we are back to the more simple practice of, for example, the third row refering to the third row, the fourth row to the fourth, and so on.

To set up the column headings we begin with the current planning year and extend this row until it matches the 30 years of our study. These years- column head years- are used in our formulas.

In each of those columns we calculate the Current Cost for any given item in any given year. But we want to show only those years when servicing any given item is scheduled to occur.

To calculate the current cost the formula is:

'Categorized Component'!Current Cost * (1 + Inflation Rate) ^ (Column Head Year - Parameters!Current Planning Year)

The first instance looks like this:

'Categorized Components'!$E3*(1+Parameters!$C$2)^(B$1-Parameters!$C$5)

When we drag this formula horizontally, all the cells will fill in the Current Cost for the year at the head of the column across the 30 years of the study.

All of those numbers are accurate, but we want those items to appear on the page only in their service year.

To do this, we subtract the planning year from whatever year is at the column head and divide that by the Useful Life. We check if there is a remainder or if there is no remainder. We are employing the modulus or MOD()function.

Writing just that part of the formula

=MOD(B$1-'Categorized Components'!$D3,'Categorized Components'!$F3)=0

Returns FALSE. The formula is checking if the remainder is 0. The actual numbers being calculated are:

(2024-2026) / 10 = -.2

The remainder is not 0. For that first row, when we get to the first replacement year of 2026, the result will be 0 and the simple Modulus formula will return TRUE. Since each year has a different useful life, results will vary row to row.

When that first item is calculated in the column with heading 2026 which, for that item, is the Replacement Year the numbers look like this:

(2026 - 2026 / 10) = 0

Likewise, in 2036, with the Useful Life at 10, the result will be 10 / 10 and once again we get no remainder- the modulus is 0.

When we put this together in the larger formula for calculating an actual value, we want the formula to run when the modulus is 0, otherwise we want the cell left blank, which in Excel is achieved with double quotes "".

We write the formula to run only when the modulus is 0 and otherwise to leave a blank cell.

=IF(MOD(B$1-'Categorized Components'!$D10,'Categorized Components'!$F10)=0,'Categorized Components'!$E10*(1+Parameters!$C$2)^(B$1-Parameters!$C$5), "")

The page is arranged in categories and there is space for summing each category, a title row for each category, and a blank space in between for readability. If the full formula is dragged across these cells, we'll get an error in those cells we want blank. To mitigate this, we wrap the IF() statement in an IFERROR() function and leave the cell blank when the error condition is met.

This is the formula used throughout the page down to the last named item:

=IFERROR(IF(MOD(B$1-'Categorized Components'!$D3,'Categorized Components'!$F3)=0,'Categorized Components'!$E3*(1+Parameters!$C$2)^(B$1-Parameters!$C$5), ""), "")

That formula can be dragged down the Column B. With all those cells highlighted, the can be dragged horizontally across the 30-year span of the study and we'll have the years filled in in their service years.

That last part of the formula, the "", could be set to enter 0. In fact, it can enter anything placed as part of the final statement of the formula.

Were we to set it to 0, when the modulus calculation is FALSE we'd get a 0 in the cell. We might want to see that to confirm the cells that should run an error are doing so.

That will leave us with a lot of 0's on the page—not so great looking.

Hide zero values

In Excel Preferences, there is a "View" option where one can check/leave unchecked a box to show/hide zero values.

Once a formula is set up for a whole page, one can check this box to confirm that zero values show up where expected.

At that point one can choose not to show zero values or, as I did in the formula with the double quotes, to enter nothing in those cells.

The last few items and the Totals row of the spreadsheet

A few columns at the bottom of the page

What we ultimately want are the totals for each year. These totals will be used on our Cash Flow page.

The image shows a few columns at the bottom of our spreadsheet. The Totals row uses the SUM() function, SUM(B1:B127) in the first instance, and this can be dragged across the row to total each column.

In these examples I have not entered totals for each category. I left the space to do so, but I have no particular need for them.

Were we to do so, we would re-write the SUM() function to add just the individual items, and then we'd have the opportunity to sum the category totals as well. They should agree, so this might be a useful check.

The Expenditures page makes it easy to see what is planned for any given year. Should one want to make changes, we do that on the Original Components page and those changes ripple through the Categorized Components and then onto this page.

Current Cost

The Current Cost for each component was calculated on the Original Components page. That was the current cost for the current year only.

Here we want to show the current cost for the first and all subsequent years. The cost will change year to year, affected by the inflation rate.

We already made this calculation for the Expenditures spreadsheet, but we hid all but the service year and checked to satisfy the IF() statement that involved the modulus. Now we're back to the simpler core of that calculation.

The Column Head Year is simply whatever year is at the top of the column on this Current Cost page.

Current Cost * (1 + Inflation Rate)^(Column Head Year - Current Planning Year)

In the first instance this is:

='Categorized Components'!$E3*(1+Parameters!$C$2)^(B$1-Parameters!$C$5)

That can be dragged down Column B, then across the full 30 years of the study.

In our example, substituting in the original cell's numbers, we have:

=30000 * (1 + .037) ^ (2024 - 2024)

That may look odd. The "power of" is equal to 0. That condition will return the original current cost with no inflation applied. The following year, (2025 - 2024) will return 1, and the inflation rate will be applied once, and so on.

When the entire study is updated for next year, we can see here what the then-new "Current Cost" amounts should be.

Highlighting the actual expenditures amounts in the correct year

The image of the page has cells with the grey background and a thin border. You will remember that the Expenditures page listed only those items in the year when they are being carried out.

We use the Excel feature of conditional formatting to apply that background and border (or whatever other setting one might want) when the amounts on this spreadsheet match up with the amounts that appear on the Expenditures page.

Conditionally format the expenditures years

We go to the Conditional Formatting section in the Excel editing options or toolbar.

We create the custom format to appear when the item in the Current Cost page matches the amount in the Expenditures page.

We do this for the full range of cells where the items costs are calculated.

This is just a helper to identify the years when the expenditures are scheduled to occur.

This conditional formatting is also used on the Fully Funded page.

The Edit Formatting Rule dialog box

Features are Style = Classic, use a formula to determine what cells to format, the formula, and the directive to format with a custom format

We again set zeros not to show at the Excel menu: Excel -> Preferences -> View.

Fully Funded

The concept of Fully Funded took me a while to wrap my brain around, and then the formula...sheesh!

Let's say we have a reserves item scheduled for the current year at a cost of $9,000. The Fully Funded amount that should be in the reserves account is $9,000, just what we need to pay the cost.

A healthy reserves account may have around 60 - 70% of the fully funded amount for all items. An item come due will nonetheless be serviced because all items are not scheduled for any one year. The reserves account is fluid regarding where the money actually is spent in any given year.

Now let's say that same item repeats on a 3-year schedule. Ignoring inflation, the following year Fully Funded amount is $3,000, 2 years later is $6,000, and then the actual cost of $9,000 is again available in the third year.

We saw how the Current Cost increases year on year because of inflation. That happens to our Fully Funded amount too, so we don't simply divide any year by its Useful Life. Inflation is also worked into the formula.

Here is the formula that we use to fill in all the cells:

=IFERROR(('Categorized Components'!$I3*((1+Parameters!$C$2)^(B$1-'Categorized Components'!$D3)))*IF((MOD(B$1-'Categorized Components'!$D3,'Categorized Components'!$F3))=0,1,MOD(B$1-'Categorized Components'!$D3,'Categorized Components'!$F3)/'Categorized Components'!$F3), 0)

Hmmm...yeah...that is one steaming pile of formula. Let's break it down.

We use the IFERROR() Excel function to enter a zero if there is an error. We will get an error where we have blank cells on the page, so we use the Excel option to not show zero's on the page to keep our spacer cells clear when we drag the formula through them and they return an error.

If something doesn't seem right with the results on the page, we can choose to show the zero's to and then track down the problem.

IFERROR(value is not met, 0)

In the center of the formula is an IF() function. The references in that function are looking to the Categorized Components page.

IF(Modulus((Column Heading Year - Replacement Year divided by Useful Life)) = 0, enter 1, otherwise enter Modulus(Column Head Year - Replacement Year divided by the Useful Life) divided by the Useful Life).

To put that more simply, let's assume a Useful Life of an item of 10 years, and let's say we have had a year when the item is serviced. The next year when we calculate the full cost of the item, we will multiply that by a modulus of 1 divided by the Useful Life of 10, so we will have 1/10 of that item's cost appear as the fully funded amount.

The following year will have a modulus of 2, so we will show 2/10 of the Current Cost. When we get to the service year, the modulus will be 0 (there is no remainder of 10/10) and dividing 0 by 10 will give us an error so we enter the numeral 1 in that case only. That is why if the IF() the modulus is 0, we enter 1.

That part of the formula will work no matter what the Useful Life is. It may be 18 years for some component, and we'll have fractions such as 1/18, 2/18, 3/18 building up to the service year when we must jump in and enter one where the formula would otherwise give us 0/18 (no remainder / 18).

These items that supply the appropriate fractions are multiplying the first part of the formula, which is our Current Cost calculation for any year. We could refer to the entries on the Current Cost page, but I prefer to keep the references to the Categorized Components page.

Fully Funded = First Future Cost * (1 + Parameters!$C$2)^(Column Year - Replacement Year)
'Categorized Components'!$I3*(1+Parameters!$C$2)^(B$1-'Categorized Components'!$D3)

This is similar to the Current Cost formula. Current Cost uses the Date in Service cost and Fully Funded uses the First Replacement Cost. Also, Current Cost uses (Column Year - Planning Year) and Fully Funded uses (Column Year - Replacement Year).

We calculate sumes for each column. The first year's sum can be compared to the total on the Original Components page and any problems with our entries tracked down.

The entire row of totals is transposed on the Cash Flow page.

The Cash Flow spreadsheet

We have worked through all the pages and now return to get the summing up of everything and view the current financial condition on the Cash Flow page.

To fill in the Current Cost totals, the total Expenditures for each year, and the Fully Funded totals, we can copy the sums from those individual pages in the workbook and enter them in the designated Cash Flow columns where they will be used in various calculations within the page itself.

Let's work through the individual columns

Beginning year

We can enter the first two years, highlight them and drag down to get the full 30 years of the study.

Current Cost

We use the tricky and touchy Excel TRANSPOSE() function to fill the Current Cost column.

We select the 30 cells in the Current Cost column and write

=TRANSPOSE(

Then go to the Current Cost page and select the total for year we're planning for, in this case 2024, and drag across the 30 years of the totals row. A helpful hint is to click the Control key when horizontal scrolling and the choice will be restricted to that row- no accidental going up or down and having the incorrect cells appear in the formula.

Complete this part of the formua with a parenthesis, then- VERY IMPORTANT- click Control -> Shift -> Enter. Clicking only Enter will not work.

I find getting the desired result the first time is tricky, but persist and we'll end up in the formula bar:

{=TRANSPOSE(B127:AE127)}

It does not work to type this (with your actual cell range) into the formula bar. One must do the Control -> Shift -> Enter.

Using a formula that makes a relative reference to the cells means that any change on the Current Cost spreadsheet totals will be carried onto the Cash Flow page.

One could also go down the column and enter, in the first case:

='Current Cost'!B$127

Followed by

'Current Cost'!C$127

and so on until all the cells are filled. I haven't figured a simpler way than TRANSPOSE() that also maintains the relative reference, but would be happy to learn one as TRANSPOSE() always gives me a hard time.

Annual Contribution

Annual Contribution = Previous Year Contribution * (1 + Contriubtion % Change)

The first instance of this is the row 2024. It relies on the contribution from the previous year which we can bring in from the Parameters page with:

Parameters!C10

This amount is then used to calculate the annual contribution by applying whatever percentage we have entered in the Contribution % Change column.

=C2*(1+I3)

That first instance can be entered and then dragged down the column to have the formula apply for the 30-year scope.

I find it useful to use the Contribution % Change to guide the budgeting for the Annual Contribution. One could also simply choose amounts year by year and change the Contribution % Change percentage to reflect whatever numbers are entered. I'll cover that when we get to a look at that last column of the page.

Annual Interest

The formula for calculating the Annual Interest is:

Parameters!$C$3(1-Parameters!$C$4)(F2+(1*('Cash Flow'!$C3-'Cash Flow'!$E3)))

The first instance, which can then be draged down the column, is:

Parameters!$C$3*(1-Parameters!$C$4)*(F2+(1*('Cash Flow'!$C3-'Cash Flow'!$E3)))

I have also seen it recommended that the last part of that formula, (1('Cash Flow'!$C3-'Cash Flow'!$E3) be written (.5('Cash Flow'!$C3-'Cash Flow'!$E3)

Annual Expenditure

We want to get the yearly totals from the Expenditures page, so we use the TRANSPOSE() function as above on the Expenditures page and end up with a formula:

{=TRANSPOSE(Expenditures!(B127:AE127)}

Project Ending Reserves

Project Ending Reserves = Previous Year's Ending Reserves + Annual Contribution + Annual Interest - Annual Expenditure

which in the first example that can then be dragged down the column is:

=(F2+C3+D3)-E3

Recall that F2 is the project ending reserves from the year previous to the study which is brought into this page with Parameters!C9.

Fully Funded

This is our final TRANSPOSE() exercise and it should something like:

{=TRANSPOSE(B127:AE127)}

except that your range will probably be on a different row.

Percent Funded

Percent Funded is where we can gauge how we're doing. As mentioned earlier, one often sees 60% - 70% funded as fairly healthy. The formula to calculate this is:

Percent Funded = Project Ending Reserves / Fully Funded

In the first instance, which can then be dragged down the column, this is:

=F3/G3

Contribution % Change

This is my own addition to the Cash Flow page. It is very useful for planning and for gauging how the rate of the Annual Contribution is increasing or decreasing.

We saw at Annual Contribution how a formula can be used so that the contribution reflects a percentage change entered in this column. If we simply want to enter figures in Annual Contribution, we can do that and then have the change reflected in the Contribution % Change column with this formula:

Contribution % Change = 1 - (Annual Contribution / Previous Year's Annual Contribution

expressed in the first instance with:

=C3/C2

And that's the Cash Flow page!

Rather than go through all the formulas again, I'll provide the following link for reference: