-4

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.

enter image description here

Keyur Shah
  • 27
  • 1
  • 1
  • 6
  • Is there not a formula in the cell? Otherwise, this seems more like a maths problem than Excel, no? – BruceWayne Jan 07 '19 at 20:19
  • No formula...I can figure out how to calculate total amount for 2018, 2019 and so on...I know how to calculate for all years... The term left is 36 on above example and 36*payment amount yields around 10496.00 – Keyur Shah Jan 07 '19 at 20:32
  • Two functions that may help are `=FV()` or `=YIELD()` as well as many other built-in functions for financial work. – David S Jan 07 '19 at 23:28
  • Thank you....For all your help...I figured it out – Keyur Shah Jan 09 '19 at 16:04

1 Answers1

0

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.

Keyur Shah
  • 27
  • 1
  • 1
  • 6