Carried Balance (interest earned)

[Cansley]

Hi. I have tired searching to no avail, so appologies if this is covered somewhere already...

I am trying to calculate the carried balance on a equity incentive award that earns a variable rate of return each year and can be paid at the end of the 4th year as a lump sum in full or in part (if in part the deferred balance continues to earn the variable rate). This is a running sum problem, but further complicated by the fact that I want the database to calculate the annual earnings each year, which are based on the prior year’s ending balance. In addition each participant gets a new award each year.

Name Award Year Award
Joe 2009 500
Joe 2010 600
Sally 2009 250
Sally 2010 300

The growth rate changes each year but is the same for all participants. In this example, if the 2010 rate was 10%, then 2010 balances would look like this (assuming no payments):

Name AwardYr Award Carried Balance
Joe 2009 500 550
Joe 2010 600 600

Award is input in a table; Carried Balance must be calculated

In 2011 (assuming that 2009 was vested now) with a rate of 5%, it would be this:
Name Award Yr Award Pmts Carried Balance
Joe 2009 500 577.50* 0
Joe 2010 600 0 630

* (550 x 1.05)

Payments will be input into a table and can be looked up, but earnings and balance need to be done in the database. I have tried using DLookup and Dsum to pull the prior year ending balance with no luck.

1st year: award x (1+rate)=ending balance
2nd/3rd years: prior year ending balance x (1+rate) =ending balance
4th year +: Prior year ending balance – cash payments x (1+rate) = ending balance

Any help would be MUCH appreciated!

Thanks

 

[PNGBill]

Have you considered using a Table to store the calculated results ?

Use custom functions to get balances at year end month end etc.

Use a loop in vba code to run through the dates for each client and calculate the interest and append same to the table.

The next loop will use the function / s and calculate the next interest and append same.

Any payouts are considered in each loop.

This then gives you the data to produce a statement with FundsIn, FundsOut, InterestEarned all available for the report.

Their are functions (excel) that will give you quite a few financial results but I don't think they work on access/vba but if you have a balance as at a given date then the interest for that Day/Week/Month/Year is quite easy to calculate.

 

[Cansley]

I did consider that. Seemed like the "long way" and I had hoped for more efficent solution but there appears to be none. Many thanks for the reply.