Personal Finance & Money Asked by user102837 on August 23, 2021
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.
You can use this formula
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)))
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
Answered by Chris Degnen on August 23, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP