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

Improve this question

1 Answer 1

Reset to default
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
2
  • 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
    Commented Sep 28, 2020 at 13:28
  • @Paul - I don't know of an inbuild Excel finance function that uses the 365 day basis.
    – Chris Degnen
    Commented Sep 28, 2020 at 13:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .