Formula to use in Excel Spreadsheet

[Kevin Yoo]

I am trying to figure out what formula to use in excel if I sold my note at a discount to calculate the new rate. Does anyone know this?

[Dion DePaoli]

Well yes.  

Like with any number and math, there are a couple different methods which produce different but related numbers.  Since "rate" is a very general word, it is not clear just what you want to calculate.

Certainly we understand that the Note 'Interest Rate' does not actually change.  The return changes for the investor related to the discount and related to time.  

I get the impression you are attempting to use the math to guide you to a Target Return which the Buyer of your note would enjoy (provided they want that number).  That might be best served as an NPV calculation.  The NPV calculation will deliver back the Sale Price based on the input Hurdle Rate.  The Hurdle Rate is viewed as Yield (Interest Income) in that setting.  

Note:  Layman calculations tend to use a Yield definition which is broader than what we use on a daily basis.  When we discuss Yield, it Interest Income only.  The amounts paid/received do not include Principal (or capital return).  

We refer to both Interest and Principal calculations as Rate of Return.  This is synonymous with Cash on Cash return.  Essentially, this is simply sum of Total Payment divided by Cost Basis.  This is the layman math used by many folks when trying to sell a return idea.  Problem is, it is misleading.  The number includes the principal being return.  So it will be GREATER than the actual Yield (interest income).

For Instance:

Loan Amount:  $100,000
Interest Rate:  7.5%
Amortization:  360
Payment:  $699.21

Rate of Return = [Payment] x 12 / [Loan Amount]
ROR = 8.39%

Now we know at par the Yield of this loan should be the same as the Interest Rate. The reason the ROR/COC calculation is skewed is it assumes that ALL of the Payment is interest income. We know that to be false. The Payment includes Principal being returned.

The most common miss used and probably widely misunderstood metric when calculating loans.  So, that covers quick and easy math.  

Assume the following:

PV = $100,000
nPer = 360
PMT = ($699.21) - ensure this number is always negative when used as formula input
Rate = 7.5%

For concept illustration purposes let's look at RATE:

Rate = ([nPer,PMT,PV,,.1) (I always just plug the guess in at 10%)
RATE = 7.76%

This rate will be off slightly because the final payment in the amortization schedule is not handled correctly.  The final payment in amortization schedules is mostly principal and no or very little interest.  So, that last payment in the array is treated incorrectly and skews our calculation up which is why our RATE is 0.26% higher than the Interest Rate.  The skew factor is a variable which changes from each calculation.  I mainly wanted the reader to see and understand the ROR is not equal to RATE and understand the skew as it relates to other calculations.

The issue with using RATE in discounted loans is accounting for the discount.  It can be done, but is more complicated than needed for purposes here.

The methods most likely to be used:

Internal Rate of Return is the most 'accurate' in the sense it handles the principal and interest portions more properly.

IRR= (P(0),P(1),P(2),...P(360))
*The easy method here is to setup the entire payment schedule from period 0 to term. In our example case that is 0 to 360.  

P(0):      [Purchase/Sale Price/PV]
P(1):      [PMT] - all positive number
P(360):  [PMT]**

IRR = 0.62% - this is a periodic return number, meaning each period, in our case, each month. Not annualized.

To annualize assume IRR answer is 'X':

A IRR = (1+X)^12-1

Incorrect Method A IRR  = (X * 12)

A IRR = 7.76%
Same issue here as with RATE.  The final payment is not $699.21.  If you plug the correct final payment in at P(360)** you will end up with the correct number unskewed.  The correct payment on period 360 can be found on an Amortization Table or calculated using other methods I will not get into.

When using IRR simply input in Period(0) the Sale Price or PV to obtain the output you desire.  Use PV when trying to find the par loan interest rate (which is the same as RATE) or use the purchase price when trying to find the discounted return.

Net Present Value is method to deliver back the cash equivalent needed to achieve the input Hurdle Rate.  For out purposes Hurdle Rate is the same as IRR and RATE.  The Hurdle Rate is a user input variable.  This allows the user to say something to the idea of:  "I want to sell this loan at a 15% IRR"

NPV = ([Hurdle Rate], P(1), P(2),...P(360))

Hurdle Rate = 15%

P(1): [PMT] - all positive number
P(360): [PMT]** - (see above skew info)

NPV = $55,298.18

So, if we wanted to sell the example loan at a Total Return to the Buyer (held to maturity and assuming all payments made on time) at 15% the sale price would be the NPV output or $55,298.18.

NOTE:  For all equations the periods must be equal throughout the calculation.  The user can use an annual payment for each 'P' or period.  So a 30 year loan would have 30 periods.  If using monthly payments then each 'P' is a month.  If monthly periods are used in each equation the user must either input the related monthly rate of interest or they can simply take the rate input and divide by 12.  

NOTE:  TIME is a variable that will affect the calculation output when a discount is present.  This happens because the discounted principal is returned faster than the amortization of the loan.  So in any of the above calculations for IRR or NPV if you reduce the number of periods and properly account for that final payment the return will vary.  This does not work for the RATE function as it automatically assumes the final payment is equal to all other payments and it can not be changed.  

This is the second thread I have put this detail in.  There is another thread in the Notes Forum here on BP that a longer discussion took place.  I can't remember the name of the thread but it is out there.  

I see tons and tons of issues and miscalculations on a daily basis from folks. The most often seen misuse is the ROR/COC calculation. That is not because the calculation is incorrect but rather the interpretation of the output is misused. Some to understand as both a buyer and a seller. Seller's often place that simple calculation in their offering to entice buyers. It's not wrong, the buyer just needs to make sure they understand what it really means.

There are TONS more details to this which become way too much to cover in this post which can and will affect the presumptions that are used when structuring a model to calculate a number.  Concepts like interest arrears and recovery of advances will change the output when  used properly.  That is, since they change the periodic payment amount.  That is also aside from two investors using the same inputs values which includes time.  As such as a Seller I would recommend using the calculation as a guide not a line in sand to consummate a transaction.  

Hope it helps.  Good luck.