Microsoft Access Financial Cash Flow Calculations with Total Access Statistics

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:

• Periodic 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.
• Irregular Cash Flows (Date Dependent)
  Each record has a date field for its cash flow payment or receipt which may not be evenly spaced over time. The amounts may also vary with each record.

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

Periodic Cash Flow Output Fields

These fields are calculated for all periodic cash flow analysis:

Field Name	Description
Group Fields	Group fields selected (if any)
DataField	Field name identifying the data in the record
Count (N)	Number of records in the group
Missing	Records with missing values
FirstCashFlow	Value of the first cash flow
LastCashFlow	Value of the last cash flow
Sum	Sum of the values (profit)
Sum-Negative	Sum of the negative cash flows (values less than zero)
Sum-Positive	Sum of the positive cash flows (values greater than zero)
These fields are created if the NPV option is selected
Rate	The discount rate you specified for each period
FV	The Future Value of the cash flows at the end of the last period
NPV	Net Present Value of the discounted cash flows
This field is created if the IRR option is selected
IRR	Internal Rate of Return
These fields are created if the MIRR option is selected
MIRR	Modified Internal Rate of Return
RateFinance	The periodic finance rate you specified
RateReinvest	The periodic reinvestment rate you specified
MIRR-PV	The present value of the negative cash flows discounted by the finance rate
MIRR-FV	The future value of the positive cash flow discounted by the reinvestment rate

As with all other values generated by Total Access Statistics, the rates (percentages) are shown as a decimal value between 0 and 1 (e.g. 5% is 0.05).

Irregular Cash Flow analysis is similar to Periodic Cash Flow analysis, except the timing of individual cash flows is taken into account.

Periodic analysis assumes the same amount of time between each cash flow. For situations where you have dates associated with each cash flow, the irregular cash flow analysis will provide a more accurate result.

Irregular Cash Flow Field Selection

The following field selection screen appears when you choose Irregular Cash Flow analysis:

Irregular Cash Flows Field Selection

This is similar to the selection for Periodic Cash Flows, except a Date Field is required to identify the timing of each cash flow.

Irregular Cash Flow Options

After selecting the fields, the Irregular options are presented:

Number of Days in Year

To adjust the interest rate to apply to cash flows between two dates, the annual interest rate needs to be divided by the number of days in a year.

365 days is the most common selection but if your cash flows span over multiple years, using 365.25 to adjust for leap year is more accurate. The difference of course is rather small.

Net Present Value (XNPV)

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. This is identical to what’s calculated in the Periodic cash flow analysis. The difference in the irregular cash flow analysis is that each cash flow is adjusted by the overall annual discount rate based on the date it occurs. NPV where dates are taken into account is called XNPV.

Unlike the periodic cash flow analysis where you specify the discount rate for each period, the discount rate for irregular cash flow analysis is always the annual rate.

Internal Rate of Return (XIRR)

The Internal Rate of Return 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.

This is identical to what’s calculated in the periodic cash flow analysis. The difference in the irregular cash flow analysis is that each cash flow is specific to the date it occurs. IRR where dates are taken into account is called XIRR.

Modified Internal Rate of Return (XMIRR)

MIRR is a modification of the IRR calculation and is a more accurate reflection of the true rate of return for a series of cash flows. MIRR where dates are taken into account is called XMIRR. To calculate XMIRR, provide the two interest rates:

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

Unlike periodic cash flows, the rates for irregular cash flows are always the annual rate.

Irregular Non-Periodic Cash Flow Output Table

Irregular Non-Periodic Cash Flow analysis is performed for each value field:

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

Total Access Statistics lets you interactively generate your calculations through its wizard interface to easily select the data source, fields, and analysis without programming. After you specify your selections, they are automatically saved as a scenario in your database so you can run them again in the future when your data changes.

A VBA statistics function is also available so you can generate these results from code. You can easily run any saved scenario by calling a single procedure in the Total Access Statistics VBA library. The library includes a royalty-free runtime license so you can include it with your Access database and distribute it to non-Total Access Statistics owners.

Call the function from a button's OnClick event or other process, and the results are generated for you. You can even hide the built-in user interface so your users don't even know Total Access Statistics is running. They'll be amazed with your statistical analysis capabilities!

Here are some additional resources and details about some of the data analysis you can perform on your Microsoft Access data with Total Access Statistics:

• Financial Cash Flow Calculations
  Use discounted cash flows and interest rates to generate Net Present Value (NPV), Present Value (PV), Future Value (FV), Internal Rate of Return (IRR), Modified Internal Rate of Return (MIRR) for periodic and date dependent payments and receipts.
• Percentile Calculations
  Calculate different types of percentiles: median, quartiles, quintiles, octiles, deciles, percentiles, every X.5 percentile, and place them in a table or update an existing field with the record's percentile value.
• Running Totals and Moving Averages
  Summarize a moving set of records to calculate running sums, moving averages, minimum, maximum, range, median, standard deviation, etc.
• Data Normalization (Data Transpose)
  Transpose non-normalized data so you can easily analyze and maintain it.
• Regressions
  Simple, multiple and polynomial regressions with calculation of equation coefficients, ANOVA and residual table
• Crosstabs and Chi-Square
  Advanced cross-tabulations with percentage of row, column and totals displayed as records or columns