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.