0
$\begingroup$

I am doing something seemingly quite easy: Prinipal calcuation of a loan.

I need to calculate daily principal payments and accumulate it on a yearly basis.

So my current implementation look like that:

enter image description here

However, implementing it in excel is not that easy.

Below you can find the logic:

enter image description here

At the moment I am calculating the first year, if the date is not the beginning or the end of the year, daily and the other years with a yearly formula, which gives me differences an accumulated difference in my solution.

Any recommendations, how to implement "daily principal payments and accumulate it on a yearly basis" in excel?

I appreciate your replies!

$\endgroup$
2
$\begingroup$

Don't be afraid to use one row in your spreadsheet for each day. Enter the date in column A, then increment the date by 1 day for each row until you reach the end of 10 years. In column B enter the formula "=YEAR(A1)" A1 being the cell with the date in it.

Then calculate your principal and interest amounts based on the interest rate divided by the total number of days in a year. Becareful to consider how the interest is compounded in the loan and modify the interest rate as required.

You will have 6 columns: Date, Year, Principal at start, Interest, Payment, Principal at end. Principal at end is the start plus interest less payment. The principal at the start of the next line is equal to the principal at the end of the previous.

Fill these formulas down until the date is equal to the last day, ten years later. 3650 rows plus a row for headers, plus a row for each leap year day Feb 29.

The sum of the interest and payment columns can be used to see what the true cost of the loan was. Alternatively you can use a pivot table to analyze the amount of interest and payment by year.

This sounds like it will take a long time, but it can be done in less than five minutes since all you have to do is set up the first couple rows and then fill it down the sheet.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.