**GENIUS NEEDED!!** Amortization sheet help

[gymbo731]

Hi All,

I need a lot of help. I've looked all over the Internet for a template for what I am looking for, but I can't find anything. I've been close but not really.

Let me explain. I am looking for an amortization formula sheet for a boat loan that I have. That is easy enough, and I found that, but here is the hard part.

I went in on the loan with 4 different people all paying different prices a month. Let me get specific - I have a loan where one person is paying $55 a month, another $30, and three more $35 each. For a total of $190/month for the boat loan.

I want to be able to keep track how much each individual person owes in principal and in interest. Those prices a month are the minimum they will pay each month, but if someone decides to make more than the minimum payment, then it will mess everyone up with the total amount that they still owe.

So basically even though it is more one big loan, it is actually a total of 5 smaller loans as well with the same interest rate, but different minimum payments a month.

Is there anyone out there that can help me?

Thank you! If you need any more information, or more specifics on the numbers, please let me know. I don't mind posting that.

Thanks again.

 

[alansidman]

You could create a summary sheet that brings the information from each of the 5 sheets to the front sheet consolidated. That would be a simple addition of cells to the front summary page. That would give you the global picture. Not sure what else you could do.

Alan

 

[DLEET]

Question, from my quick calculation, the person who pays $55 per month had an original amount owing of approximately $4,793.

What if they decide to pay his full outstanding amount off in one month - his next payment would be $0 since he is paid in full, yet there is still a requirement to pay a total of $190 per month - who picks up the shortfall?

 

[Jacophile]

Hi, I think the loans can be treated separately.

The principal owing (i.e. the lender's equity) at the end of month k is P_k. At the start of the loan, month 0, this was P₀ which is the amount borrowed.
Each payment r_k = dP + I_k
where I = m*P_k-1
where dP is the reduction in principal and I is the monthly interest due, m being the monthly interest rate.

Each month the principal is reduced by
dP_k = r_k - m*P_k-1

That means the loan amount at month k is

P_k = P₀ - sum(dP_j)
or
P_k = P₀ - sum(r_j - m*P_j-1)

where the sum is from j=1 to k

Say for instance there are 3 borrowers in the loan (to save typing) and that

P₀ = A₀ + B₀ + C₀

and more generally,

P_k = A_k + B_k + C_k

and that each party makes payments of a_k, b_k and c_k on month k

So the amount owing can be calculated as

P_k = P₀ - sum(a_j + b_j + c_j- m*P_j-1)

which after a little algebra becomes

P_k = A₀ - sum(a_j - m*A_j-1)
+ B₀ - sum(b_j - m*B_j-1)
+ C₀ - sum(c_j - m*C_j-1)
= A_k + B_k + C_k

In other words you can treat it as 3 (or in your case 5) separate loans of A₀, B₀ and C₀.

You just need a contract between the parties that each is solely liable for their section of the loan.

 

[Rockymtnhigh]

Gymbo,

Go to youtube.com and type in "Excel Finance Trick #17" in search. Michael Girvin has a killer amort template and video. Yes, would need to make different worksheets for each of the boat partners, but this schedule has added field to make additional payments on principal. It is for a home purchase, but you can adapt for boat, car etc

 

[mortgageman]

The ownership is not equal. It is split by the amount they pay each month.

So I would still have to have 5 different tables? There isn't a way I can have them all in one sheet to keep track of?

A) Assuming each person pays the same min payment each month, you do not need separate tables. Just pro-rata the p and I each month
B) This is the tricky part, and requires more than just a spreadsheet - it requires a discussion among the parties - if one person pays more than their pro-rata min payment, they are paying more principle - AND THUS INCREASING THEIR EQUITY STAKE. Is this okay with the other partners?
What if in one month, one partner pays an extra amount equal to the outstanding balance? Does s/he now own a majority of the boat?