How to calculate year over year Loan yield until the maturity. ?

Question

My finance guy asked me to help with his broken spreadsheet and I cant figure out the logic/formula behind it..

I have Remaining Principal, Loan Rate, Maturity Date, Data create Date, Payment Frequency and I would like to calculate the total yield for year over year.

For Example, Data create date is Dec 31, 2018. The Remaining pricipal is 11500. Loan rate is 9.99% Payment Frequency is Bi-Weekly. Payment amount is $350.

I would like to know how much money I am expecting in the year 2018, 2019 and 2020.

I can attach Excel if that helps.

Answer 1

I have created a formula that calculates yields based on the year and subtract the amount from previous year until the maturity date is reached.

If anyone need help with similar situation, feel free to reach me.