Calcule a borrowing rate called TIC

Michael Keith
Michael Keith used Ask the Experts™
on
I need some help coming up with a present value formula to calculate what we call in our industry the true interest cost of debt or TIC.   In the attached file is the calculation produced by software that we use however it does not provide formulas.   I would like to come up with a spreadsheet that I can use to calculate TIC on my own.   About the spreadsheet that I have provided.   At the top is the solution to the cash flows below.   The dated date is the date that interest begins on the loan the TIC is the effective borrowing rate or the rate necessary to discount the amounts payable on the respective principal and interest payment dates to the purchase price (loan amount) received assuming compounded semi-annually.   The first column is the dates payments are made, the second column is the amount of debt paid each semi period and the third column is the discount column calculated by the software to come up with the TIC (discount rate) that equals the target amount above of $10,513,798.96.  I would like to setup a spreadsheet that will allow me to calculate on my on the TIC.  Meaning if I know the target amount, Dated Date, and semi-annual cash flows the spreadsheet will calculate the TIC that discounts those cash flows so that they equal the target amount.  I have tried using the IRR function but it did not work for me.  Hoping to get other ideas how to set this up using this example to check that it works by trying to recreate the answer.  I hope this make sense but please ask questions.
C--Users-keitm01-Desktop-Proof-of-T.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
There are a couple of other functions that I am aware of.

Copied from online help page:

PV function      
PV, one of the financial functions, calculates the present value of a loan or an investment, based on a constant interest rate. You can use PV with either periodic, constant payments (such as a mortgage or other loan), or a future value that's your investment goal.

NPV function      
NPV is similar to the PV function (present value). The primary difference between PV and NPV is that PV allows cash flows to begin either at the end or at the beginning of the period. Unlike the variable NPV cash flow values, PV cash flows must be constant throughout the investment.
I figured out how to back in to the calculation I was looking for.  Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial