Interest calculated daily compounded monthly

[bradles]

Does anyone know how I can work out a formula to calculate how long it will take to pay a loan back to zero if the interest is on a loan is calculated daily but compounded at the end of the month. To add another level of complexity, assume you are making fortnightly repayments.

P (Principal) = $500,000
E (Fortnightly Repayments) = $2,500
r (annual interest rate) = 8.59%
t (daily compound interest rate) = $$\frac {0.0859}{365}$$

I started out with the following:

$$A_0 = P$$

$$A_1_4 = P-E$$

$$A_2_8 = P-2E$$

$$A_3_0 = A_2_8 + 14(A_0 * t) + 14(A_1_4 * t) + 2(A_2_8 * t)$$

$$= P - 2E + 14(Pt) + 14(Pt-Et) + 2(Pt-2Et)$$

$$= P-2E+30Pt-18Et$$



and continuing...

$$A_4_2 = A_3_0 - E = P-3E+30Pt-18Et$$

$$A_5_6 = A_4_2 - E = P-4E+30Pt-18Et$$

$$A_6_0 = A_5_6 + 12(A_3_0 * t) + 14(A_4_2 * t) + 4(A_5_6 * t)$$


$$A_1_4$$ and $$A_2_8$$ is where I make repayments. $$A_3_0$$ is where the interest is finally compounded.

Am I heading in the right direction for this?

Can anyone enlighten me on the best way to go about solving this?

 

[HallsofIvy]

Does anyone know how I can work out a formula to calculate how long it will take to pay a loan back to zero if the interest is on a loan is calculated daily but compounded at the end of the month. To add another level of complexity, assume you are making fortnightly repayments.

P (Principal) = $500,000
E (Fortnightly Repayments) = $2,500
r (annual interest rate) = 8.59%
t (daily compound interest rate) = $$\frac {0.0859}{365}$$

There is no reason to "calculate the interest daily" if you don't do anything with it! If the interest is being compounded monthly, then calculate it monthly: monthly interest is 0.0859/12= 0.00716.

I started out with the following:

$$A_0 = P$$

$$A_1_4 = P-E$$

$$A_2_8 = P-2E$$

$$A_3_0 = A_2_8 + 14(A_0 * t) + 14(A_1_4 * t) + 2(A_2_8 * t)$$

$$= P - 2E + 14(Pt) + 14(Pt-Et) + 2(Pt-2Et)$$

$$= P-2E+30Pt-18Et$$



and continuing...

$$A_4_2 = A_3_0 - E = P-3E+30Pt-18Et$$

$$A_5_6 = A_4_2 - E = P-4E+30Pt-18Et$$

$$A_6_0 = A_5_6 + 12(A_3_0 * t) + 14(A_4_2 * t) + 4(A_5_6 * t)$$


$$A_1_4$$ and $$A_2_8$$ is where I make repayments. $$A_3_0$$ is where the interest is finally compounded.

Am I heading in the right direction for this?

Can anyone enlighten me on the best way to go about solving this?[/QUOTE]
I'm not sure I understand this. The interest is compounded monthly but how often are you paying on the loan?

 

[bradles]

Let me explain. I will change from a straight 30 days being one month though...just to make things more complicated...sorry.

You start out with a loan of $500,000. Each day 8.59%/365 * $500,000 accrues in interest for 14 days until you make a payment of $2,500.

So the balance at day 14 is $500,000 - $2,500 = $497,500 (with $1647.39 in interest accrued so far).

For the next 14 days the loan accrues 8.59%/365 * $497,500 in interest. When you make a payment on day 28 the balance is $497,500 - $2,500 = $495,000 (with $1,639.16 interest accruing on the $497,500 for those 14 days).

Lets say that day also happened to be the compound date (the day the interest is actually charged and capitilised into the loan. The balance would be:

$495,000
+ $1,647.39 (interest accrued in the first 14 days)
+ $1,639.16 (interest accrued in the second 14 days)
------------------
$498,286.55

Then the cycle continues.

Banks here calculate interest daily and charge it to the loan on the 28th of each month. You can choose to make repayments monthly, fortnightly or weekly, however interest is still accrued daily and charged on the 28th of each month.

I was wondering if there is a way you could calculate things like what your repayments would need to be to pay off a loan of this type in "x" years. Or to work out how long it would take you to pay off a loan of "x" amount repaying "y" every fortnight. etc.

Hope that makes sense.

Brad

 

[sennyk]

Calculate the average daily balance. And use that as your principal for each month. Most credit card companies calculate interest this way.

 

[HallsofIvy]

Now I see why you want to calculate the interest daily- the principle changes in the middle of the month.
But you can still do it "monthly". Since for half the month you owe $500000 and for half the month $497500 so for the entire month you owe an "average" of (500000+ 497500)/2= $498750. Calculating $498750*0.0859/12 will give you approximately what you have. "Approximately" because 28 days is not exactly one month.

 

[bradles]

I'd like to try and develop a formula for it...but I'm not sure it's possible. I can simulate it on a spreadsheet but it requires a lot of data and it gets slow in the calculations.

I thought I could develop a formula to save all the number crunching.