Microsoft Access does not include financial functions to analyze cash flow data in your tables. Performing such calculations has been quite challenging for MS Access users and developers. Fortunately, Total Access Statistics performs a wide range of cash flow analysis:

• Use discounted cash flows to generate Net Present Value (NPV), Present Value (FV), Future Value (FV), Internal Rate of Return (IRR), Modified Internal Rate of Return (MIRR) for periodic and date dependent payments and receipts
• Analyze the data in your Access tables and queries (including linked data)
• Support grouping on field values so you can perform multiple analyses in one pass
• Generate results in Access tables that you can use in your queries, forms, and reports

Best of all, by using a database approach with support for groupings, calculations for each group of records is automatically generated making it far more powerful than performing the calculations in Microsoft Excel. Since the results are in Access tables, you can add them to your forms and reports.

Financial Calculations were added to Total Access Statistics for Microsoft Access 2010, and X.8 versions for Access 2007 and earlier.

Financial Analysis Overview

If you have a table with records containing your cash flow information, Total Access Statistics can perform the financial analysis you seek. Easily calculate discounted values and returns for a series of cash flow investments and receipts. Negative values are considered investments (payments) and positive values are receipts (returns).

Two Financial scenario types are available depending on whether your data represents Periodic or Irregular cash flows:

Each record is a cash flow payment or receipt that is identically separated in time from each other (for instance, annual or monthly transactions), but the amount may vary for each period.

Periodic Cash Flows

The simplest and most common cash flow analysis is based on periodic transactions. Payments or receipts can vary in size but occur on a regular schedule. Most common intervals are annual, monthly, and quarterly. Depending on the frequency of your cash flows, you’d enter the appropriate interest rate for each period.

Periodic Cash Flow Field Selection

The following field selection screen appears for Periodic Cash Flows:

The Value Fields contain the cash flow data. Each field you selected is analyzed separately. The values in the field should be negative for payments (investments or outflows), and positive for receipts (returns or inflows).

Group Fields let you generate a separate set of analysis for each combination of unique values among the group fields.

The Sort Field is used to ensure your cash flows are sorted properly. This may be the AutoNumber field of your table or another field that can be used to ensure Total Access Statistics processes each cash flow in the right order. It is critical that your cash flows be in the right order.

If no group field is specified, the Sort Field is not required and Total Access Statistics analyzes your records in their natural order, which may or may not be okay. You need to verify this.

You can optionally specify a Weight Field which can be used in situations where you have a field for the receipt amount and another weighting field that specifies the quantity of those receipts.

Periodic Cash Flow Options

After selecting the fields, the Periodic options are presented:

Cash flow timing for each time period

For Cash Flow analysis, it’s important to determine whether the first item represents an event at the beginning of the time period or at the end of the time period. For instance, if your initial investment (a negative number) occurs on Day 1, choose the Beginning option. If the first investment occurs at the end of the first year, choose End.

The implication of this selection can be most easily understood with the first investment. If it’s at the beginning of the period, the first cash flow is not discounted since there hasn’t been any time to apply the interest rate. If it’s at the end, it is adjusted by the discount rate.

Net Present Value (NPV)

Net Present Value is the current value of a future series of payments and receipts and a way to measure the time value of money. Basically, money today is worth more than money tomorrow. And future money is discounted by the interest rate you specify.

Assuming cash flows occur at the end of each period, an NPV with a 10% discount rate would divide the cash flow of period 1 by (1 + 10%) then add the cash flow in period 2 divided by (1 + 10%) ^2, etc. The NPV calculation ends with the last cash flow. The formula for NPV is:

where N is the number of periods, n is a specific period, C is the cash flow for a particular period and r is the discount rate for each period.

Set the Discount Rate to Your Time Interval

The discount rate you enter must correspond to the period of time between each cash flow. For periodic cash flow analysis, Total Access Statistics does not have date information on when the events occur.

If it’s monthly, you should use 1/12th of your annual rate, for quarterly one-fourth, etc. For most accurate results, take the n th root of your annual discount rate to capture the compounding effect.

Internal Rate of Return (IRR)

The Internal Rate of Return is used to measure an investment’s attractiveness. It is the interest rate that makes the NPV equal to zero for the series of cash flows. At least one negative payment and one positive receipt are required to calculate IRR. If this doesn’t exist, the result is null.

IRR is sometimes called the discounted cash flow rate of return, rate of return, and effective interest rate. The “internal” term signifies the rate is independent of outside interest rates.

Depending on the number of cash flows and their values, IRR can require many iterations to generate an accurate result. Microsoft Excel stops after 20 tries. Total Access Statistics generates its standard level of double precision accuracy and will do so for up to 1000 iterations.

Modified Internal Rate of Return (MIRR)

Modified Internal Rate of Return is used to measure an investment’s attractiveness. MIRR is a modification of the IRR calculation and resolves some problems with the IRR.

IRR assumes that positive cash flows are reinvested at the same rate of return as that of the investment. This is unlikely as funds are reinvested at a rate closer to the organization’s cost of capital or return on cash. The IRR therefore often gives an overly optimistic rate of the cash flows. For comparing projects more accurately, the cost of capital should be used for reinvesting the interim cash flows.

Additionally, for projects with alternating positive and negative cash flows, more than one IRR may be found, which may lead to confusion.

To use MIRR, provide the two interest rates:

• Finance Rate: the cost of capital
• Reinvestment Rate: the interest received for cash investments

Similar to the discount rate provided for NPV, these rates should be the rate for each period and not the annual rates if your periods are not yearly. The formula for MIRR is:

where n is the number of equal periods at the end of which the cash flows occur (not the number of cash flows), PV is present value (at the beginning of the first period), FV is future value (at the end of the last period).

MIRR sums the discounted negative cash flows to the starting time, and sums the positive cash flows to the final period adjusting for the reinvestment rate. By dividing and taking the n th root, it determines the rate of return for the positive and negative cash flows.

Note that in Excel or VBA, the MIRR function always assumes the cash flows are at the beginning of the period. If you want to use the End of period option in Total Access Statistics and compare it to Excel, add an extra cash flow of zero to the beginning of the Excel data set.

Periodic Cash Flow Output Table

Periodic Cash Flow analysis is performed for each value field:

Example of Periodic Cash Flow Analysis Results in a Microsoft Access table