Amortization spreadsheet without due dates?

I'm trying to find an Excel amortization template that doesn't function based on a monthly due date.

Basically I need one that will calculate the correct interest whether 2 payments of varying amounts are made in the same month, months apart, or somewhere in between. 

I don't begin to have the Excel chops to make/modify a template like that & honestly my math skills suck & I'm doubtful I could comprehend the logic required anyway. 

Anyone know of something that will do this? Google hasn't helped. 

This isn't too hard, and I'm happy to help, but need some more information because what you've stated here doesn't quite make sense. 

Are you looking for an amortization schedule that allows for random extra payments on principal on random dates? 

How is the interest compounded (daily, weekly, monthly, annually)?

How many extra payments within payment periods could there be? 

You've got mail to your Yahoo account. 

Looks like mtn has this well covered.

At it's core, you just need to have a multiplier for periodic interest that is as adaptable as possible.

Assuming you'd never make more than one payment per day, you could create a formula that looks like this example.

Annual interest rate:        5%

Daily interest rate:           0.000137  (0.05/365)

Principle:                           $1,000

Daily interest amount:    $0.137  (13.7 cents per day)

Period:                                18 days

Formula:                            $1,000 X 0.137 X 18 = $2.47  (in plain English, it costs you $2.47 to borrow $1,000 for 18 days)

Now, if you paid $50, $47.53 would go to reducing principle so the principle during the next period would be $952.43.

In reply to RX Reven' :

The part that I'm stuck on is that the period in your example is variable, but I want to make sure the interest continues to accrue in the interim period. I've not found any spreadsheet templates that work that way & I'm not sure how to do it myself. 
 

Basically, if we start with $5000 principle at 5% interest I need a spreadsheet I can populate like this: 

Row-1: Date 1/1/2021, Amt. $50, Amt. toward principal, Amt. toward interest, Remaining balance

Row-2: Date 2/1/2021...

Row-3: Date 4/17/2021...

In reply to mtn (Forum Supporter) :

Thanks, that is perfect!

Pete Gossett (Forum Supporter) said:

In reply to RX Reven' :

The part that I'm stuck on is that the period in your example is variable, but I want to make sure the interest continues to accrue in the interim period. I've not found any spreadsheet templates that work that way & I'm not sure how to do it myself. 
 

Basically, if we start with $5000 principle at 5% interest I need a spreadsheet I can populate like this: 

Row-1: Date 1/1/2021, Amt. $50, Amt. toward principal, Amt. toward interest, Remaining balance

Row-2: Date 2/1/2021...

Row-3: Date 4/17/2021...

I understand, I intended "period" to be variable rather than fixed...just change "period" to "days since last payment" and the formula will still work.

I don't want to step on mtn's efforts, let's let him have a go at it first.

Take care for now,

Brett

RX Reven' said:

Pete Gossett (Forum Supporter) said:

In reply to RX Reven' :

The part that I'm stuck on is that the period in your example is variable, but I want to make sure the interest continues to accrue in the interim period. I've not found any spreadsheet templates that work that way & I'm not sure how to do it myself. 
 

Basically, if we start with $5000 principle at 5% interest I need a spreadsheet I can populate like this: 

Row-1: Date 1/1/2021, Amt. $50, Amt. toward principal, Amt. toward interest, Remaining balance

Row-2: Date 2/1/2021...

Row-3: Date 4/17/2021...

I understand, I intended "period" to be variable rather than fixed...just change "period" to "days since last payment" and the formula will still work.

I don't want to step on mtn's efforts, let's let him have a go at it first.

Take care for now,

Brett

Step away, you're getting more in depth than I am.  

I sent Pete something on it. We could get it much more exact than it is, if we wanted to get down to a daily interest and calculate when the payment was made, I just did the simple version: (Balance-Payment)*(1+Monthly rate). If the payment is 0, then the balance goes up. 

I was personally getting a little bit confused because it isn't an amortization schedule at all - there is no schedule. It is really a balance calculation on an "open" line of credit. It could, theoretically, stay open indefinitely from what Pete has described. 

In reply to mtn (Forum Supporter) :

Once you brought that up, yeah, it truly is more like a rent-to-own type situation more than a proper mortgage. Though the arrangement seems to be working well enough. 

mtn & Pete,

I'll set something up in Excel and e-mail it to both of you.

Brett

In reply to RX Reven' :

Would you please copy me on that as well?  I enjoy messing with Excel and this could be a useful tool. Thanks. 

Yeah, I'd be interested in that as well

vdblyou at yahoo

Hive,

I've sent an e-mail to all those that have requested a copy of the Excel spreadsheet I setup for Pete.

Please let me know if you didn't get it.

I totally thought that was a spam email. Going to dig through my junk emails....

In reply to RX Reven' :

Thanks again!

chandler said:

I totally thought that was a spam email. Going to dig through my junk emails....

Hey chandler, here's the content of an e-mail I just sent you... 

Please let me know if you didn't get it.

************************************

Added later...

To all, don't copy rows seven or eight as they "kick" everything off.

Rows nine and below are stable so they can be copied and the information in the "Instructions for use" box are valid for them only.