Some details on Balloon Payments...

[Debeer165]

So then, I'm trying to write up an excel sheet that shows monthly balances on a car loan, which adjusts either the repayment required over the same period or adjusts payment period while keeping value constant, if one were to add in x amount extra over your standard monthly payment.

Gotten it running well, everything on the actual setup works, from the interest calculations on the base through to the deposits and extra payments. Only issue comes with the balloon, not sure where to subtract it and where to add the interest back.

Eg. 100k loan, 20% balloon. You pay the 20k at the end of term, but what happens to the interest on that 20k? Is it added at the start of the term (ie starting first month with higher interest balance), or what? I'm using the financial calc from Wesbank to check that everything works out correctly but doesn't match up with their values on monthly payment once the balloon is removed.

Tried starting off the term with 80k balance, + the interest of the balloon over 60 months but then the monthly payment is too high, tried changing a few things and it ended up too low....

Any excel, motor dealers, or finance gurus who might be able to assist?

 

[akescpt]

Tagging this. I hope you sharing this spreadsheet! Would be very useful.

 

[creeper]

20% is the end amount left after the initial period. in other words. You pay from 100% to 20% of the capital off.

To calculate your repayments, use PMT(interest/12, installment period in months, how much capital you will owe at the beginning aka present value, future value should be 20% of Present value). The trick is that you do pay interest on the full amount, but the payments are lowered by shifting the 20% outstanding capital to the end.

 

[Ancalagon]

I suggest playing around with the FNB calculator to see if you can replicate the way it works. But yes, very interesting and please share it.

A while ago, I worked on a spreadsheet (dont think I save it) to compare the idea of keeping your monthly payments as low as possible by having a large balloon payment and a long repayment term, vs a shorter repayment term and no balloon payment. Some say that because cars depreciate so quickly, you had best "rent" them in any case. Myself, I'm not sure.

 

[Debeer165]

Mmmkay I see I see. Seems I had basically taken the balloon off the balance and added back the interest of that at the start. I'll change it by having a separate column which has balloon value in used for interest calculation, but with the remaining balance without it showing.

At college today but eh it's a Monday near the end of term so it'll be slow, should have it finished before too long.

Where should I upload, Google Drive, Dropbox, etc?

 

[Electric]

Expected a thread about balloons.....

 

[akescpt]

Mmmkay I see I see. Seems I had basically taken the balloon off the balance and added back the interest of that at the start. I'll change it by having a separate column which has balloon value in used for interest calculation, but with the remaining balance without it showing.

At college today but eh it's a Monday near the end of term so it'll be slow, should have it finished before too long.

Where should I upload, Google Drive, Dropbox, etc?

anywhere we can get our grubby paws on it. thanx!

 

[ice_cubes]

lol@ balloons!

 

[Electric]

lol@ balloons!

So much dissapoint...

 

[hellfire]

Interest is calculated on outstanding balance including the balloon payment

 

[Debeer165]

20% is the end amount left after the initial period. in other words. You pay from 100% to 20% of the capital off.

To calculate your repayments, use PMT(interest/12, installment period in months, how much capital you will owe at the beginning aka present value, future value should be 20% of Present value). The trick is that you do pay interest on the full amount, but the payments are lowered by shifting the 20% outstanding capital to the end.

Tried this. The payment value keeps increasing as the term progresses which isn't right.. Formula is
PMT(constant interest, period length - current period, remaining balance excluding balloon, constant balloon value, type 0)

I suggest playing around with the FNB calculator to see if you can replicate the way it works. But yes, very interesting and please share it.

A while ago, I worked on a spreadsheet (dont think I save it) to compare the idea of keeping your monthly payments as low as possible by having a large balloon payment and a long repayment term, vs a shorter repayment term and no balloon payment. Some say that because cars depreciate so quickly, you had best "rent" them in any case. Myself, I'm not sure.

Any tips on what you did in terms of formatting perhaps?

 

[creeper]

Tried this. The payment value keeps increasing as the term progresses which isn't right.. Formula is
PMT(constant interest, period length - current period, remaining balance excluding balloon, constant balloon value, type 0)

PMT is used to calculate your installment over a fixed period. If you want to know the interest over two periods, investigate the IPMT function. Lesser known formula.

So, if you want to calculate balloon, here is a bit of a quick and dirty instructions:

Have a cell for Interest rate. Let's call it B2
Have a cell for the period (months). B3
Have a cell with the balloon %: B4
Have the cost of the debt: (use negative) amount B5. (also known as present value)
Have the final outstanding value: B6 = B4 * B5 (also known as future value)
If you want to, have B7 be your additional payment.

The PMT calculates your installment for a vehicle: PMT(B2/12,B3,B5,B6,0). Store in Cell B8. The reason why you divide the interest by 12, is that most interest are represented as annual. This value will give you your monthly installment.

Now the fun part. There is another great function called NPER. Number of periods. Again, NPER(B2/12,B8+B7,B5). This formula is sticky one and pops an error quite easily. What makes it great is that it calculates the amount of periods if you pay an certain amount more / month.

Now regarding the balloon value. You can refinance it, then all you do is the PV become the outstanding value and FV is 0)

Another nice formula is calculating your interest for a period. So, it works the same as PMT, but another parameter to state which period you want to calculate the interest for.

I did this in a hurry over lunch, so please forgive me if I made any mistakes. It does get tricky if you don't have excel open

Edit: Forgot to mention, there is another technique that is a bit longer to do, but allows a nice graph to show why you need to pay more in at the beginning to pay less interest later. E.g. First payment can lead to 2 less payments at the end etc. etc.

 

[ProfA]

Interest is calculated on outstanding balance including the balloon payment

+1

 

[Garson007]

The reason why you divide the interest by 12, is that most interest are represented as annual.

Shouldn't it rather be ((1+B2)^(1/12)-1)?

Even then, do banks work by month or by day/second?

 

[hellfire]

Shouldn't it rather be ((1+B2)^(1/12)-1)?

Even then, do banks work by month or by day/second?

Daily balance, with interest accrued monthly

 

[Ancalagon]

Any tips on what you did in terms of formatting perhaps?

It didnt have much in the way of formulas.

But what I was really wanting to compare was the various different ownership options and the financial implications of each - to find out the cost of ownership over the ownership period, whether that be 3 years or 5 years.

So I tried to estimate what the resale value of a particular car would be. To do that, I took the price of a car new and used an online inflation calculator to date it back 3 years (or 5 years, depending on ownership period). That would give me the new purchase price. For the used resale price, I found the cheapest online price and then subtracted about R10k, to account for resale losses. Could easily be higher. In cases where the exact car being offered had changed, I used one that was similar in its position in the lineup.

Anyway, the rest of the information can be obtained using an online vehicle finance calculator, although the one you are writing will do much the same. Add deposit and loan term x loan repayment, then subtract resale value and balloon payment (if any). The amount left over (which should be positive) will be how much you actually spent on the car in that time, not including services (if no motor plan), tires, brakes, taxes and fuel.

Resale value after 3 years tended to be about half the purchase price when inflation is accounted for.

 

[creeper]

Shouldn't it rather be ((1+B2)^(1/12)-1)?

Even then, do banks work by month or by day/second?

That is if you want to calculate interest over a year, charged monthly, paid annually.

Saw the other post, yes, banks charge daily, acrued monthly.

 

[Aghori]

A balloon payment means at the end of the finance period, you're screwed unless you pay that huge amount of money. Pic related.





View attachment 109018

 

[Electric]

Nice pearl necklace

 

[Garson007]

That is if you want to calculate interest over a year, charged monthly, paid annually.

But why quote it then as an annual rate? B2 is most certainly not the annual rate if you're getting charged B2/12 monthly.