Calcule a borrowing rate called TIC

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
LVL 1
Michael KeithAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
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.
0
Michael KeithAuthor Commented:
I figured out how to back in to the calculation I was looking for.  Thanks.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Software

From novice to tech pro — start learning today.