2

Future value of a loan: Interest calculated daily on a 365 day basis. Interest is compounded monthly. Fixed interest rate. Fixed term.

Is there a way to calculate what the loan balance will be in 1 years time using a formula. Instead of working through 12 months loan calculations on individual excel rows.

Spreadsheet calculations

1

You can use this formula

equation

where s is the principal, i is the rate, and dk is the number of days in month k

demonstrated here using Mathematica.

d[1]=  9; d[2]= 31; d[3]= 30; d[4]=31; d[5]= 31;
d[6]= 28; d[7]= 31; d[8]= 30; d[9]=31; d[10]=30;
d[11]=31; d[12]=31; d[13]=30;

t[n_] := s Product[1/365 (365 + i*d[k]), {k, n}]

s=50000;
i=0.055;

t[13]
52892.

Or in Excel. Ironically this only seems to work in Excel 365.

=C1*PRODUCT(1/365*(365+C2*A1:INDEX(A:A,B1)))

enter image description here

Longhand version

s = 50000
i = 0.055

s/365^13*(365 + 9 i)*(365 + 31 i)*(365 + 30 i)*
  (365 + 31 i)*(365 + 31 i)*(365 + 28 i)*
  (365 + 31 i)*(365 + 30 i)*(365 + 31 i)*
  (365 + 30 i)*(365 + 31 i)*(365 + 31 i)*(365 + 30 i) = 52892.02
| improve this answer | |
  • Im sure that this response will be useful to the OP. I would like to add that there are also some "built-in" financial functions in Excel for doing this sort of thing. The "FV" function (i.e. = FV(....)) may be particularly useful for him/her, and they might also refer to other financial functions such as =PPMT(...), =IPMT(...), etc. – Paul Sep 28 at 13:28
  • @Paul - I don't know of an inbuild Excel finance function that uses the 365 day basis. – Chris Degnen Sep 28 at 13:36

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.