• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Interest calculation for the payment made

M

minesh

New Member
Hi All,

I am facing a situation where a company "A" bears all the expenses of company "B". In return company "B" has to pay interest of 25% pa on the payment made by company "A" on its behalf. Company "B" repays back whenever it has funds back to company "B". which includes principal as well as interest. In year 2013, interest was calculated on day, whereas in 2014 its calculated at end of every month on the payment made by company "A". I need a excel working, so that when company "B" repays back it should eliminate the outstanding payable by company "B" from the beginning with interest.

Regards
Minesh
 

Attachments

Hi Minesh,

I'm guessing we are wanting to look at worksheet "Upto Dec 31,2013" for the data?
Data is for years 2012-2013, but your post mentioned 2014. Is there more data somewhere, or a typo somewhere?
Is the interest compound or simple?
You appear to already have a formula that's calculating the interest...When Company B starts making payments, do they have to pay off all the interest on all items that have accumulated, or just the first one?
 
Luke M said:
Hi Minesh,

I'm guessing we are wanting to look at worksheet "Upto Dec 31,2013" for the data?
Data is for years 2012-2013, but your post mentioned 2014. Is there more data somewhere, or a typo somewhere?
Is the interest compound or simple?
You appear to already have a formula that's calculating the interest...When Company B starts making payments, do they have to pay off all the interest on all items that have accumulated, or just the first one?
Click to expand...

Hi Luke,

if you notice 2012-13 interest is calculate on days wise, in 2014 i want same to be calculated in end of month( i.e if amount is borrowed at beginning of the month or at end, but interest will be calculated at end of the month). Its simple Interest calculation. It could be better if i can get interest on all items that have accumulated
 
If Date of Loan is in A2, and Today's date is in F1, then formula would be:
=LoanAmount*25%/12*DATEDIF(EOMONTH(A2,0),$F$1,"m")
 
thanks Luke for formula. But what if we receive repayment in between which includes interest and principle
 
Then we would need to factor that in, hence my long list of questions originally. How to you currently account for the payments coming in?
If money is borrowed in Nov, Dec, and Jan, and a payment is made in Feb, does that payment go towards the interest on all 3 before applying to Nov principal, or does it pay off Nov's interest and principal, and then move to Dec's interest?
 
If we receive repayment in feb, it could pay interest from nov till jan. Balance left out should be principal of nov , dec & jan respectively
 
Hmm. I had to redesign the layout of the calculator a bit to keep track of amounts. In new layout, you input into the blue cells, and record in appropraite column whether it's an amount being borrowed (paid by comp A), or if it's an amount being paid back on loan (paid by comp B).

then, the 3 calculation columns figure out amount of interest earned since last table entry, current principal balance, and current interest balance.

Control for Interest rate is at top

Total amount oustanding on the loan is show at top.

Table will automatically expand as new entries are added, so no need to update formulas.
 

Attachments

Hi Luke,

As per your calculation, at the end of 2013 interest balance is 102,563.58, but the actual interest should be 106,504.Since, we are calculating till 2013 on day wise. Can you help me in sorting it out. I Have highlighted it in yellow
 

Attachments

Hi Minesh,

As you didn't say how you calculated what the "actual interest" should be, I have no way of error checking the formulas to see what is wrong. Please advise.
 
Hi Luke,

Thanks for your reply!!!

For year 2013 it was to be calculated on day wise interest and for 2014 it was on monthly basis. Even the formula which u created the condition applies, but the only problem for 2013 in the formula you created is when there is two or more payments on same date, interest gets calculated for the only the 1st principal amount not for the consequent principal. For your reference I have attached interest calculated only for 2013. May be because of that we getting different interest
Regards
Minesh
 

Attachments

Ah, I see now. The problem was that in your file, it was using the date in cell F1, which was 12/31/2013. In my file, in row 90, you are only at date Dec 21, 2013. To get a comparison, change the date in your file in cell F1 to be Dec 21, 2013, and you'll see that the interest calculates out to be the same.

Your file tries to calculate interest for every line for total duration. If you want a running ledger where principal can be applied to pay down previous debts, and have a different rate kick in at 2014, you'll need to go my route, where the interest is a running total, telling you how much there is as of the date listed.

I've also attached my file, with a spacer entry added in at Dec 31, 2013, to futher show that the calculations line up.
 

Attachments

You must log in or register to reply here.
Back
Top