VBA Annuity Application
An Excel tool that calculates the future value of a growing annuity — built so a non-finance user cannot get a wrong answer by accident.
- Role
- Developer
- Focus
- Automation & usability
- Language
- Excel VBA
- Context
- Ontario Tech University
Overview
A growing annuity is a series of payments that increases by a fixed rate each period — a contribution that rises with inflation, for example. Its future value is straightforward to state as a formula and easy to get wrong in a spreadsheet, because a single mistyped rate produces a confident-looking number with no warning.
This project wraps that calculation in an Excel VBA application with a guided form, so the user supplies inputs through a controlled interface rather than editing raw cells, and the tool refuses inputs that would produce a meaningless result.
The calculation
The application computes the future value of a growing annuity from four inputs: the initial payment, the interest rate per period, the growth rate applied to each successive payment, and the number of periods. The implementation also handles the edge case where the growth rate equals the interest rate — the standard formula divides by zero there and needs a separate expression.
Input validation
Validation is the part that turns a formula into a tool. Rather than trusting whatever lands in the input cells, the application checks entries before any calculation runs.
- Rejected non-numeric and blank entries before they reach the calculation
- Guarded against value combinations that make the formula undefined
- Returned specific, readable messages so the user knows which field to fix
The user form
A graphical user form replaces direct cell entry. Each input is labelled with what it means rather than which variable it maps to, so someone who has never seen the underlying formula can still complete the calculation correctly. The form was designed and then tested with use in mind — the goal was measurably fewer input errors, not a nicer-looking sheet.
Debugging and documentation
The VBA code was debugged and optimised for reliable performance and accurate output across input ranges, not just the happy path. Functionality and usage instructions were then documented so the tool could be handed to an end user and adopted without the author sitting beside them.
What the build taught
- Input validation is what separates a working formula from a tool someone else can safely use.
- The growth-equals-interest case has to be handled explicitly or the calculation divides by zero.
- A guided form reduces user error more effectively than instructions telling people which cells to edit.
- Documentation is part of the deliverable — an undocumented tool does not get adopted.