How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Lane Your Own Question
Lane
Lane, JD, CFP, MBA, CRPS
Category: Finance
Satisfied Customers: 10098
Experience:  Law Degree, specialization in Tax Law and Corporate Law, CFP and MBA, Providing Financial & Tax advice since 1986
1929974
Type Your Finance Question Here...
Lane is online now
A new question is answered every 9 seconds

How do I calculate simple interest on a open-ended principal

Customer Question

How do I calculate simple interest on a open-ended principal loan that fluctuates up and down?
Submitted: 1 year ago.
Category: Finance
Expert:  Richard replied 1 year ago.

Hi. My name is ***** ***** I look forward to helping you.

Can you provide me just a bit more information? Does the principal fluctuate or does the interest rate fluctuate? Or both? Thanks.

Customer: replied 1 year ago.
Just the principle fluctuates. The interest rate is fixed. It's an open ended revolving note.
Expert:  Richard replied 1 year ago.

Thanks for following up. You would need to create a spreadsheet with an amortization table. And, you would have to adjust the principal balance each time you either made a payment or drew down money from the loan. And, then on your spreadsheet, you then accrue the interest on the amount of the new principal balance rather than the prior principal balance.

Customer: replied 1 year ago.
How do you create an amortization table? We are somewhat familiar with excel.
Expert:  Richard replied 1 year ago.

I can set one up for you with all the formulas if you would like. I would have to do that for you through the Additional Services option offered by JustAnswer. If that would be helpful, let me know and I'll extend that offer and you can then decide whether or not to accept. Fair enough?

Customer: replied 1 year ago.
Depends how much. We haven't got very much for 41 dollars.
Expert:  Richard replied 1 year ago.

I'm sorry you feel that way. I tried to lay out specifically how this would be set up. But, I don't want you to feel you did not receive you money's worth. The money you have posted so far is simply a deposit and has not been applied to this question. So, you haven't paid anything yet for this question. I will now opt out to open your question up to all experts so another expert can hopefully timely provide you the information in a more descriptive manner. Please do not respond to this post as it will only slow the process of such an expert picking up your question. Take care.

Customer: replied 1 year ago.
We already have an amortization page all we need an answer to what formulas to use to calculate daily interest.
Customer: replied 1 year ago.
To farther explain we can calculate and figure out interest on our own. We were wondering if excel has a feature to expedite the process. This not a regular loan with an amortized payback. Principle amounts are credited and debited on a daily basis with a fixed interest.
Customer: replied 1 year ago.
The best way we can figure out is to calculate the average principle balance on the note and figure interest off of that using total number of days. Is there a better way on Excel?
Expert:  Lane replied 1 year ago.

Hi,

...

I think this mught be an excellent application of the Excell function, "CUMIPMT."

...

I'm mguessing you've used the "PMT" function (which has 3 required inputs and two optional)

...

PMT function, which calculates the payment amount for a loan. PMT has 3 required and 2 optional arguments:

...

rate - The interest rate for the loan (as a monthly rate)

nper - length of lons, (in months)

pv - loan amount (Present value)

...

(Then there are two optional variables)

...

fv - a future value say you have a balloon amountdue at the end of the loan. When you DON'T enter this your saying that the loan is to be paid down to 0. ... and then,

type - whioch allows you to specify if payments are made at the beginning of each period, (the default being END of p[eriod of you omit this one.. You can specify 0 in this position to explicitly state that payments are made at the end of each period.

...

SO that's the conventional fixed payment loan function

...

See the image attached: ... Next I'll deal with CUMIPMT

Expert:  Lane replied 1 year ago.

What you see there is a monthly payment of $586.04 for 36 months is required to pay back $20,000 at an interest rate of 3.5 percent. (The PMT function always returns a negative amount because Excel sees the payment as an outflow), and I left out the two optional variables

Expert:  Lane replied 1 year ago.

...

CUMIPMT is similar, but uses SIX variables

...

rate - monthly rate.

nper - ength of loan in months.

pv - present value.

start_period - The starting month from which to calculate interest on the loan. Use 1 to calculate interest from the start of the loan, or 13 to calculate interest for just the second year of the loan.

end_period - The ending month through which to calculate interest on the loan. Use the same value as the nper argument to calculate interest for the life of the loan, or 24 to calculate interest for just the second year of the loan.

type - Specify 0 to indicate that payments are made at the end of the period, or 1 for payments made at the start of the period

...

See the attached image:

Expert:  Lane replied 1 year ago.

So, the CUMIPMT functionborrowing $20,000 at 3.5 percent for 36 months will cost $1,097.50.

...

and not shown, however, is the cost for the second year of the loan is $368.55.

...

To get this, calculate the principal paid back for a given portion of the loan, use "CUMPRINC," which use the same sequence of variables (arguments) as "CUMIPMT."

Expert:  Lane replied 1 year ago.

Finally - what you're doing here is using worksheet functions to calculate interest and principal for all or part of a loan, without creating a full-scale amortization schedule.

...

Hope this helps get you started

...

Lane

...

If this HAS helped, and you don't have additional questions on this, I'd appreciate a positive rating (by clicking the stars or smiley faces on your screen) ... that's the only way I'll be credited with a portion of what you've paid JustAnswer.
...

Lane

.

Expert:  Lane replied 1 year ago.

Hi,

….

I'mjust checking back in to see how things are going.

….

Didmy answer help?

….

Letme know…

….

Lane

...

Customer: replied 1 year ago.
We worked with it and tried it but that doesn't seem like the right answer. We have several open ended notes with say a 6 month due date. We add to it sometimes on a daily basis and sometimes we pay it down on a daily basis within the 6 month time frame.Sometimes it can be several days in between the next transaction. The interest is fixed. There are no amortized payments, the note is due in full at the end of the period, whatever that may be. We figured out how to do interest by computing on a daily basis but the way we are doing it seems cumbersome. It seems to me Excel should have a function to simplify. Basically we are computing simple interest on a daily basis.
Expert:  Lane replied 1 year ago.

Nothing other than simply building a spreadsheet that charges interest and adds to the balance daily (give the daily compounding) and has a column for deducting FROM that running total any payment received

...

Give me a few and let me see if I can put something together

Expert:  Lane replied 1 year ago.

OK, this is very spartan, but gets the job done (loan amount and interest are variables) and you can add a payment on any of the 180 days.

...

I've uploaded here: https://app.box.com/s/73cwgetf8c462shfx16flxpbvs0w0wmi

...

Hope this helps, or at least points out how simple the math is here... You can certainly start with this and customize

...

If this HAS helped, (and you don’t have additional questions on this), I'd really appreciate your positive rating … (by clicking or touching the stars or smileys on your screen) … … That’s the only way I'll be credited a portion of what you've paid JustAnswer.

...

Thank you,

Lane

Customer: replied 1 year ago.
It says this box has been removed.
Expert:  Lane replied 1 year ago.

Hang on ... let me look

Expert:  Lane replied 1 year ago.

NOt sure what's going on ... don't use that one a lot ... it says "shared" but then the URL is not IN the box when I click on it

...

hang with me and I 'll try google dive

Customer: replied 1 year ago.
Ok I got it. Thanks. Will study it and get back to you later. This looks quite simple, but at first glance looks like an example of compound daily interest. That is paying interest on accumulated interest. Perhaps it would be better for me to email you our sample spreadsheet.Herm
Expert:  Lane replied 1 year ago.

The only way they'll let us share personally identifiable info like emal and phone is through what's called an additional services offer

...

And yes I read your comment "Basically we are computing simple interest on a daily basis." to say compounding on a daily basis.

...

I'll make the offer for the smallest amount possible ($5) and then we will both get an encrypted box where we can share contact info confidentially ... be glad to take a look ... AND let me make an adjustment to mine

Expert:  Lane replied 1 year ago.

OK take a look

...

https://drive.google.com/file/d/0B6IsEXr9My-gX0dfMVBpSWlJNlk/view?usp=sharing

...

Payments are positive, additional amount borrowed would be a negative number here

Expert:  Lane replied 1 year ago.

So,

...

What do you think?