Create invoice with daily interest spread sheet

[Guest]

I would like to creat a invoicing/billing spreadsheet in which the balance increases or decreases with every entry and adds on a daily interest rate to all outstanding balances. I would like to add charges as they occur and subtract charges as they are paid and continue to calculate a total balance with the daily interest rate. I guess it would be similar to a cresit card account. Any advice is greatly appreciated

 

[Norman Harker]

Hi vpowers!

A bit sketchy on the information!

The main difficulty is getting the right rate.

Assuming the rate basis you are using is APR12 (aka Annual Nominal
compounded monthly) the calculation of a new balance will be:

=PrecedingBalance*(1+((1+APR12/12)^(12/365)-1))^(NewBalDate-PrevBalDate)

Hope that helps but if not post back with a little more detail on
layout.

 

[Guest]

Thank you for your response, that was about what I was attempting, I see my error's thru you formula! I am still at a loss as to where in the formula I input the compounding interest rate.
--
vpowers
'Thought I was a Pro?"

Hi vpowers!

A bit sketchy on the information!

The main difficulty is getting the right rate.

Assuming the rate basis you are using is APR12 (aka Annual Nominal
compounded monthly) the calculation of a new balance will be:

=PrecedingBalance*(1+((1+APR12/12)^(12/365)-1))^(NewBalDate-PrevBalDate)

Hope that helps but if not post back with a little more detail on
layout.

 

[Norman Harker]

Hi vpowers!

Perhaps it's better for you to keep the interest rate calculation out
of the formula.

If you have an APR12 (Annual Nominal Compounded Monthly) use:

=(1+APR12/12)^(12/365)-1

If you have an annual effective use:

=(1+AnnEff)^(1/365)-1

You now have a cell with the daily effective rate in it. (Say B2)

The formula to calculate interest on each balance becomes:

=(PrecedingBalance*(1+B2)^(NewBalDate-PrevBalDate))-PrecedingBalance

A new balance is calculated using

=PrecedingBalance+NewFlow+Interest

The art in these calculations is to sign the cash flows appropriately:

Example:
Initial debt is negative
Initial balance is positive
Credits are positive
Debits are negative
Interest will automatically take the sign of the preceding balance
that it is calculated on.


I've got one set up if you'd like a copy. Just post request to email
below.