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.

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?

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.

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?

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

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

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

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."

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. ...

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.

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

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.

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

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