Content:
This is a call our Support team gets often. A customer is trying to reconcile their Excel worksheet to their amortization schedule in TValue and they ask “Why doesn’t TValue agree with my Excel schedule?”
The answer is usually that the formula’s methodology in Excel needs to be replicated in TValue so we have the same calculation basis to get the same numbers. This would be step one. Once this is resolved and if there are differences, it is often the formulas in Excel that are the issue as the user has copied and pasted, written an incorrect formula, or has inconsistent dates in Excel versus TValue. Generally, we find that TValue calculates the correct schedule 100% of the time with the same assumptions.
What are some of the things to look for when reconciling from Excel? First is the computation methodology. Is it monthly, exact days, a 365-day year or a 360-day year, or a 30/360 calculation? Is the interest compounded or not (simple interest)? Usually, you can get this information from the formula if you have the Excel file. You can determine if the calculation is counting days, calculating a full month (Monthly), and the year length.
If you don’t have the Excel file, start by reconciling the first interest charge. You can just do a simple calculation in Excel as shown below to verify the method that was used for the first interest calculation.
| Balance | Rate | Period/Days | Year | Interest |
|---|---|---|---|---|
| $50,000.00 | 5.0% | 1 | 12 | $208.33 |
| $50,000.00 | 5.0% | 31 | 365 | $212.33 |
When you have the assumptions, it is easy to set up TValue with the compounding/rate period, the interest rate, the year length, and the payment schedule. Once you have reconciled the methodology and you have reconciled the first interest calculation, you are halfway home. The next interest expense should also tie out to TValue. If not and you have the Excel file, you might want to check the Excel formula and the dates. There is probably a discrepancy that you can see.
The process is usually pretty easy. If you have any questions or need any help using TValue software, please give our Support Team a call at 800-426-4741 or email support at support@TimeValue.com.
