Thread: Compound interest problem

Hi, Can anyone help me solve a financial question please. If someone borrows money and agrees to pay back at 'say' 5%. It is possible to borrow more, and payback any time. Using Excel, I want to know how to have a running totat where 1st column is month, 2nd column is pay in, second column is The ammount total, 3rd column is borrow more. I hope this makes sense. Camerart

I have re-read my question and it certainly is written badly, let me try again.

I have attached an example excel sheet, in the hope it will explain better than words. I am trying to fill in the total each month.

Originally Posted by camerart

Hi, Can anyone help me solve a financial question please. If someone borrows money and agrees to pay back at 'say' 5%. It is possible to borrow more, and payback any time. Using Excel, I want to know how to have a running totat where 1st column is month, 2nd column is pay in, second column is The ammount total, 3rd column is borrow more. I hope this makes sense. Camerart

You don't give very much information. I assume the "5%" is annual interest rate but are you paying back monthly, annually, or at the end of a given time? If you are paying it back, what is the amount of the payment each period? Is the interest compounded and, if so, how often? All of those are things that lenders are required, by law, to tell borrowers.

note that this forum is not for posting problems ...

Originally Posted by HallsofIvy

You don't give very much information. I assume the "5%" is annual interest rate but are you paying back monthly, annually, or at the end of a given time? If you are paying it back, what is the amount of the payment each period? Is the interest compounded and, if so, how often? All of those are things that lenders are required, by law, to tell borrowers.

Hi Hallsofivy,

I hope I can explain it better this time?

This is an example only, I used 5% but it could be any percentage. I use excel.

Example: If a sum was borrowed in January, by each January it would be +5% and so on. The borrowed amount could be displayed at each month throughout the years.

If further sums were borrowed and paid back in any month. Is there a calculation for this please.

I have tried using 3 columns across: In, Amount, out. With each month on each row down. This is the equation I have used so far. [=Amount/100*0.41+Amount+In-Out] Each month.

Cheers, Camerart.

Originally Posted by camerart

I hope I can explain it better this time?

Sorry...but you made it worse clarity wise....

You "appear" to be talking about a "line of credit" loan where borrower
gets advances anytime he wishes, up to an approved limit.
A set percentage (your 5% ?) of balance owing would be the expected payment.

If that's the case, all you can do "program/formula" related is
after-the-fact, like a statement of the transactions.

Originally Posted by Wilmer

Sorry...but you made it worse clarity wise....

You "appear" to be talking about a "line of credit" loan where borrower
gets advances anytime he wishes, up to an approved limit.
A set percentage (your 5% ?) of balance owing would be the expected payment.

If that's the case, all you can do "program/formula" related is
after-the-fact, like a statement of the transactions.

Hi Wilmer,

I'm struggling to explain it better, perhaps that's why I'm not so good at Maths, and English too it seems.

Maybe if I simplify it to: What is the formula, where a balance accrues a set interest over a year, and is updated and shown each month.

This would look like a list with monthly divisions and the balance would have monthly interest added to it, which after 12 months would be the set interest added.

I have attached a txt sheet to show what it should look like, all I need is the formula to enter into excel.

I hope this is better??

Cheers, Camerart.

OK; I see; simply calculating and adding interest each month:
works exactly the same if a savings account.
$1000 over 1 year at 5% annual looks like this:

Code:

0                  1000.00
1      4.17        1004.17
2      4.18        1008.35
3      4.20        1012.55
....
11     4.31        1046.80
12     4.36        1051.16

The monthly calculation uses 1/12 of rate, hence .05/12;
example month#3: 1008.35 * .05/12 = 4.20

The formula to give the future value is A(1 + i)^n :
for 1 year (12 months): 1000(1 + .05/12)^12 = 1051.16

OK?

Dear Wilmer,

Thanks for your answer.

I have a couple of problems: I entered it into Excel, and I have attached the result, what am I doing wrong?

Also I see that your equation runs for a set length of time, in this case 12months. If you look at my effort on the same excel attachment, it runs as long as I wish. The reason I need help is if you look at my equation, the '0.41' needed for the % has to be found each time I change %. I just keep changing it till it shows the correct answer after 12months, but I'm sure it caould be done with the proper equation, at least I hope so.

Cheers, Camerart.

Originally Posted by Wilmer

OK; I see; simply calculating and adding interest each month:
works exactly the same if a savings account.
$1000 over 1 year at 5% annual looks like this:

Code:

0                  1000.00
1      4.17        1004.17
2      4.18        1008.35
3      4.20        1012.55
....
11     4.31        1046.80
12     4.36        1051.16

The monthly calculation uses 1/12 of rate, hence .05/12;
example month#3: 1008.35 * .05/12 = 4.20

The formula to give the future value is A(1 + i)^n :
for 1 year (12 months): 1000(1 + .05/12)^12 = 1051.16

OK?

Originally Posted by camerart

Also I see that your equation runs for a set length of time, in this case 12months. If you look at my effort on the same excel attachment, it runs as long as I wish. The reason I need help is if you look at my equation, the '0.41' needed for the % has to be found each time I change %. I just keep changing it till it shows the correct answer after 12months, but I'm sure it caould be done with the proper equation, at least I hope so.

NO, NOT for a set length of time: n can be any number; I picked 12 to illustrate.
Take 27 months; then : 1000(1 + .05/12)^27 = 1118.81

I can't open your attachment: can you repost it using .txt (as the previous one).

Originally Posted by Wilmer

NO, NOT for a set length of time: n can be any number; I picked 12 to illustrate.
Take 27 months; then : 1000(1 + .05/12)^27 = 1118.81

I can't open your attachment: can you repost it using .txt (as the previous one).

Ok, Here it is again.

OK; able to load it; initial comment: what a mess!!

Should look more like this:

Code:

0                        1000.00  |                          1000.00
1   1000.00/100*.41667   1004.17  |  1000.00*(1+.05/12)^1    1004.17
2   1004.17/100*.41667   1008.35  |  1000.00*(1+.05/12)^2    1008.35
3   1008.35/100*.41667   1012.55  |  1000.00*(1+.05/12)^3    1012.55
...
11  1042.46/100*.41667   1046.80  |  1000.00*(1+.05/12)^11   1046.80
12  1046.80/100*.41667   1051.16  |  1000.00*(1+.05/12)^12   1051.16

You're using .41; should be more precise, like .41667 as I'm using.
The "by formula" (right side) is the proper way to use the FORMULA;
got no idea what you tried to do!

Thanks Wilmer,

" initial comment: what a mess!!--got no idea what you tried to do! " Answer, I struggle to do this kind of thing, and appreciate your help.

Anyway, I've added another attachment. You should see that I have altered it a little to match my initial question, where I can add amounts and remove amounts on any month.

Let me know if it works, in your world, please.

Cheers, Camerart.

Originally Posted by Wilmer

OK; able to load it; initial comment: what a mess!!

Should look more like this:

Code:

0                        1000.00  |                          1000.00
1   1000.00/100*.41667   1004.17  |  1000.00*(1+.05/12)^1    1004.17
2   1004.17/100*.41667   1008.35  |  1000.00*(1+.05/12)^2    1008.35
3   1008.35/100*.41667   1012.55  |  1000.00*(1+.05/12)^3    1012.55
...
11  1042.46/100*.41667   1046.80  |  1000.00*(1+.05/12)^11   1046.80
12  1046.80/100*.41667   1051.16  |  1000.00*(1+.05/12)^12   1051.16

You're using .41; should be more precise, like .41667 as I'm using.
The "by formula" (right side) is the proper way to use the FORMULA;
got no idea what you tried to do!

Looks ok...from what I can "see" ...

Originally Posted by Wilmer

Looks ok...from what I can "see" ...

Ok. good. Thanks very much for your help wilmer.

All the best Camerart. UK.