# Doubt about how to calculate loan feasibility in Excel

Copper Contributor

# Doubt about how to calculate loan feasibility in Excel

Hi everyone. I'm a rookie working in my first business case and I have to calculate the feasibility of loaning a manufacturing device to the customers. The (example) inputs I have are basically:

> Price of the device: \$ 550.000 (five hundred thousand)

> Monthy depreciation rate: 0,8%

> Contract period: 24 months

Which function could I use to calculate the montlhy payments the customers would have to pay to loan this medical device? I thought I could use the =PMT() function:

> Rate: 0,8% (monthly depreciation rate, since I can't think of any other rate to use)

> NPER: 24 (24-month contract period)

> PV: 550.000 (price of the device)

Does this make sense, does anyone have any suggestion? Any help would be appreciated.

2 Replies

# Re: Doubt about how to calculate loan feasibility in Excel

If this is your first business case, as in "I'm in school taking a business accounting class and this is my first case," which is definitely what it sounds like, then you need to do it on your own, or by taking advantage of whatever resources the school gives you.  There are resources on-line that you can to do your own research. Here's one. But we're not here to do homework for folks.

If it's not that, come back and ask again, but even then, show a bit of initiative in trying things out; you'll learn faster, leave that rookie status behind sooner.

# Re: Doubt about how to calculate loan feasibility in Excel

@IgorMakarov  .... First and foremost, you should use whatever methods that your instructor presents for such problems. It is not uncommon for instructors to present methodologies that differ from real-world methods in order to simplify problems and to teach a particular academic principle.

Since we do not have that context, we might easily misdirect you based on real-world experiences.

Second, full-disclosure:  I have very limited knowledge about depreciation and equipment leasing. Much of my comments are based on quick google searches. As you might know, the internet is rife with misinformation, often by people who also have limited knowledge.  But I try to rely on sites that I judge to be reliable.

Also, some details might depend on local regulations and even religious practices (e.g. Islamic law, if applicable). And an employer might have established practices.

With those caveats in mind, take the following "with a grain of salt" (i.e. some doubt). Treat it more as a guide to the facts that you need to clarify, if only for yourself, not so much as a turnkey solution. Other students or work colleagues might be better sources of relevant guidance.

-----

@IgorMakarov  wrote: ``Does this make sense [...]?``

Not according to my limited knowledge.

I would suggest that you get a better understanding of depreciation methods and equipment lease pricing methods.

-----

@IgorMakarov  wrote: ``I thought I could use the =PMT() function [....] Rate: 0,8% (monthly depreciation rate [...])``

Probably not.

The depreciation rate should be used to calculate the "residual value" of the equipment. That is, the value of the equipment at the end of the lease term (24 months).

There are various methods of depreciation and, therefore, of determining and applying the depreciation rate. Two of the simplest methods are: straight-line and declining balance.

For straight-line depreciation, a rate of 0.8% might mean that the value of the equipment declines by \$4400 = 0.8% * 550,000 per month.

Therefore, after 24 months, the residual value is \$444,400 = 550,000 - 24*4400.

(But usually, straight-line depreciation is described as a periodic dollar amount, not a percentage rate.)

For declining-balance depreciation, a rate of 0.8% might mean that the residual value after 24 months is \$453,568.43 = 550000 * (1 - 0.8%)^24.

-----

In any case, the monthly lease payment might be determined by:

=PMT(moneyFactor, 24, -550000, residualValue, 1)

(OTOH, the determination of the monthly payment might not use PMT at all.)

There are 4 points to note.

1. The "type" parameter is 1, not zero. For leases, payments are made at the beginning of the period, not at the end (type=0).

2. The "fv" parameter is the aforementioned residual value, not zero.

3. The "rate" parameter is the lease "money factor". That might be the "cost of capital", but probaby not the depreciation rate. Typical choices for the cost of capital:

(a) The equipment lender's "weighted average cost of capital" (WACC).

(b) The equipment lender's expected "return on investment", taking "time-value of money" into account. That might be arbitrary; e.g. based on market competition. Or it might be based on an interest rate that the equipment lender is paying on a loan to purchase the equipment, plus a profit margin, for example.

4. The sign of the PMT function result and the "fv" parameter (residualValue) should be the same. And it should be the opposite of the sign for the "pv" parameter (550,000).

However, the choice of sign is arbitrary. As a personal preference, I choose the signs so that the financial function (PMT) returns a positive value.