# Compound interest problem

Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last
• Jul 13th 2012, 01:09 AM
camerart
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
• Jul 14th 2012, 06:12 AM
camerart
Re: Compound interest problem
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.
• Jul 14th 2012, 06:49 AM
HallsofIvy
Re: Compound interest problem
Quote:

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.
• Jul 14th 2012, 08:01 AM
skeeter
Re: Compound interest problem
note that this forum is not for posting problems ...
• Jul 14th 2012, 10:09 AM
camerart
Re: Compound interest problem
Quote:

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.
• Jul 16th 2012, 04:30 AM
Wilmer
Re: Compound interest problem
Quote:

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.
• Jul 16th 2012, 06:37 AM
camerart
Re: Compound interest problem
Quote:

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.
• Jul 16th 2012, 08:01 AM
Wilmer
Re: Compound interest problem
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?
• Jul 17th 2012, 12:31 PM
camerart
Re: Compound interest problem
Dear Wilmer,

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.

Quote:

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?

• Jul 17th 2012, 01:25 PM
Wilmer
Re: Compound interest problem
Quote:

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).
• Jul 17th 2012, 10:45 PM
camerart
Re: Compound interest problem
Quote:

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.
• Jul 18th 2012, 04:11 AM
Wilmer
Re: Compound interest problem
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!
• Jul 18th 2012, 10:50 AM
camerart
Re: Compound interest problem
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.

Cheers, Camerart.

Quote:

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!

• Jul 18th 2012, 11:37 AM
Wilmer
Re: Compound interest problem
Looks ok...from what I can "see" ...
• Jul 18th 2012, 03:09 PM
camerart
Re: Compound interest problem
Quote:

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.
Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last