Calculate payments based on APR

[Kenneth Brody]

Robert T. Repko (R Squared Consultants) wrote:
[...]
> First let me say thank you for working with me on this Ken, I really 
> do appreciate it and I hope I don't sound argumentative because I am not.
> 
> The difference might be small but I didn't expect that much of a 
> difference and the difference to be the opposite of what I 
> expected.  I could be wrong but my concern is using this in 
> production and I found out the hard way my calculations are wrong.
> 
> Lending institutions calculate compound interest on a daily basis 
> instead of monthly basis for one reason they make more money on interest.

Ah.  You're not talking about making daily payments, but rather compounding 
interest on a daily basis, but still making monthly payments.  That's a 
different thing entirely.

> If I compound on a daily basis the interest is compounded 30 times by 
> the end of the month instead of once if it compounded monthly.
> 
> If I pay the 1000.00 loan off on day 1 I pay 1002.94.  If I don't 
> make a payment on day 1 but pay if off on day 2 I pay 1005.88.  The 
> longer I wait to pay the more I pay in interest which makes my 
> payments higher.  If I pay monthly the I will pay more if the 
> calculation are done on a daily basis vs monthly basis.  If I quote a 
> monthly payment I expect the payments to be higher if I compound 
> daily vs monthly.

What you need to do is not calculate payments, but calculate future value 
based on zero payments.

If I recall, they use a 360-day calendar, assuming 30 days per calendar 
month.  These examples use that assumption.  Change them as needed if I am 
wrong here.

You need to take the APR and divide by 360 for the daily interest rate. 
Then, you need to calculate the future value with the TVM_FV() function, as in:

   AmountDueAfterXDays = TVM_FV(NumberOfDays,APR/"360",PresentValue,"0")

For example, with the 13.9% used before:

   due = tvm_fv(numdays,"13.9"/"360","1000","0")

This shows how much is owed after "numdays" days, assuming no payments were 
made.  Note that the payoff amount after 1 day is not $1002.94 as you use in 
the example above.  The $2.94 "daily payment" amount includes paying down 
the principal, in addition to accrued interest.  The amount of actual 
interest after 1 day is 39 cents.  If you go 30 days (one "credit card" 
month), the balance would be $1011.65, which gives an annualized rate of 
13.98% on the 13.9% APR.  If you were to not pay for the entire year (and 
they didn't charge penalties for the same of this calculation), then the 
amount owed would be $1149.09, which amounts to a 14.909% rate.

-- 
Kenneth Brody