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.
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 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.
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.
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??
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:
The monthly calculation uses 1/12 of rate, hence .05/12;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
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
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.
OK; able to load it; initial comment: what a mess!!
Should look more like this:
You're using .41; should be more precise, like .41667 as I'm using.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
The "by formula" (right side) is the proper way to use the FORMULA;
got no idea what you tried to do!
" 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.