Team-BHP - Car Loan - EMI - Varying Interest Rates

All,

While contemplating on a car loan, many dimensions come into picture which bank ? What should be the loan amount ? Should I go for a fixed interest or variable ? Should I go for 3, 5 or 7 years ?. While doing my research recently, I zeroed in on SBI loan that offered varying interest rates (i.e., 8% for year 1, 10% for years 2&3 followed by floating rates for years 4&5). With these rates, I wanted to see how my EMI equations look like with different down payment options. Unfortunately, I didn't come across a good excel or any tool that offers me to play around when interest rates are multiple (may be I didn't google well). I was landing up at tools that gave me loan ammortization schedule when the ROI is fixed across the loan term.

So I made a makeshift excel myself after making a regular Loan Amortization Schedule, that I downloaded from the net, as the base. Don't remember from where I downloaded the original sheet but due credit to its authors :).

Sharing the excel that I developed for me. It might benefit some of you.

Caution/Disclaimer - This is a highly customized sheet. I have hidden many unwanted rows etc., Edit only yellow highlighted cells.

How to Use? -
1) This is useful when you want to see your numbers while going for a 5 year loan and the interest rates are different for each year.
2) Edit ONLY yellow highlighted cells in the first 5 tabs based on your requirement.
3) Once done, see the summary in the last tab.

Thank you Torquarius.
This really helped me a lot. I was able to simply arrive at the figure at the end of 5th year. Also was able to calculate the net benfit given by my company on the interest front.
The sheet is really help full for people like me who are Just struggling for a good loan deal.

@Torquarius - Great find. I have taken the liberty to make changes to your spreadhseet and also added an option to compare fixed and floating rate loans, in addition to the overall rate at the end of the tenure. check the Summary tab - Hope you guys find it useful.

Btw - I had to make change to your PMT formula to generate -ve cashflows in order to use an IRR function.

Hope you guys find this useful in your decision making process. :thumbs up

Best - ML

Quote:

Originally Posted by Motoringlover (Post 2245901)

Thank you Motoringlover, This sheet has helped me furhter in finalizing the loan. I am circling TATA Capital for my buy. Will update the details very soon on the decision making process.