Formulas to "layer" in new auto loans each year...PLEASE HELP!

[guruexcel]

Hi everyone,

I have a question that I'm hoping someone can help me with. I know it may sound simple, but here it is:

I have to create an auto loan model for a new business, and each year I have to assume the business picks up new customers who sign on to auto loans.

For example, from 2014-2019, the business takes on 100 new auto loan customers each year. New customers only come online once each year, and it doesn't have to be in the same month each year.

I created a simple auto loan amortization schedule that I want to link each year's new customers too, but I don't know the best way to "layer" in each year's new customers without creating some massive formula.

I'm desperately looking for a formula or a way to layer in each year's new customers while still keeping the revenue coming in from the previous year's new customers (average auto loan payoff is assumed to be five years).

Can someone please help?

Thank you.

 

[PaddyD]

Hi weclome to the board.

General approach: Start with the simplest model, then refine where is doesn't relect the real world enough.

So:

1) Inputs:
On a sheet somewhere, set up all your assumptions - at least, I assume, number of customers, average loan size, interest rate, booking month, loan term. One set of entries for each year of the business plan

2) Calculations
On a new sheet, set up enough columns for the full period of your business plan. For each annual cohort of receivables, do your calculations for each month of the plan.

3) Results
Add it all up at the bottom etc.

Has beneft of keeping things out in the open, making your assumptions transparent and easy to alter. Calculations with lots of 'smarts' embedded can look impressive but are a pain to test and maintain.

 

[guruexcel]

PaddyD,Thank you for your quick reply! This is very helpful. I have an additional question: What if I want to "stagger" the cohorts - for example, if 100 people get a loan in January 2014, but they get one year before they have to start paying off the loan (we are thinking about interesting promotions - not necc 1 year, but a few months) - do you know a formula that can help me start their loan payment in January 2015 and let that cohort loan flow through the model for five years? Thank you again for your help. Much appreciated!

 

[PaddyD]

That can get moderately complicated quite quickly - e.g. do you genuinely mean 'interest & payment free', or would you look to take minimum monthly payments out of the principle before the interest kicked in? from a credit risk perspective, i would never do the former, especially on a small loan portfolio. If customers have a year before you ask for your money, they've plenty of time to skip / forget / change circumstances before you would notice and it would only take a few bad loans to kill portfolio profitability.

Before you start getting 'fancy', i'd recommend you get a better handle on the underlying financial dynamics of a 'vanilla' offer so you can undertsand your margins of safety. (e.g. what's minimum profit you need, what delinquency & losses & operational costs do you expect, what's minimum implied interest rate / fees to achieve targets etc etc).

that said, in general:

- construct a payout curve that describe how minimum payments reduce principle balance over the interest & payment free period. If this period is really both interest & payment free then you've got a flat line (i.e. balance at start = balance at end)
- start the usual pmt calcs from when int & payment kicks in