so, it will need to return a 0 for revenue during a month the contract is not active of course. So, to calculate the following (and all following) months payments, take that 329.21 monthly principal amount and subtract it from the loan balance, which. I'd like to create a work flow which converts my report of contract lines into a monthly amortization report.Īlso, the list of contracts will overlap time frames. for the example above, if I booked the same contract on 01FEB2020 than the February revenue would be $2,000 and the remaining months would be $1,000. Generate a loan amortization schedule based on the details you specify with this handy, accessible loan calculator template. However, if the contract is booked after the start month, the back revenue is realized in the month it is booked. How to Use Excel Formulas to Calculate a Term-Loan Amortization Schedule Term loans can have a variety of repayment periods, interest rates, amortizing methods, and so on.

As in, a 1 year contract booked on (or before) 01JAN2020 for $12,000 with contract line start date of 01JAN2020 and end date of 31DEC2020 will yeild $1,000 in revenue for each of the 12 months in 2020. Loan Amount 0 Rate of Interest 0 Loan Tenure Months Months Months No of EMIs.

My company realizes contract revenue on a monthly amortized basis. It has the contract start date, end date, created date and contract value. 1, Loan Amortization Schedule, This spreadsheet creates an amortization schedule. I have data in which each row is a contract line.

Amortization schedule in months excel