Wikipedia:Reference desk/Archives/Mathematics/2020 November 15

Welcome to the Wikipedia Mathematics Reference Desk Archives
The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

November 15

Help with Excel spreadsheet formulae (interest compounded daily)

Hi all. I'm a bit of a math dunce. I need (for reasons I won't get into) to set forth in a spreadsheet, for certain exact periods of time, a fairly compact Excel spreadsheet, showing interest applied to a total of principal at certain interest rates compounded daily. Of course, the principal amount is an exact figure down to cents, and the periods of time the rates apply are not whole years, but for purposes of this question let's just use a whole numbers, say $100K, and apply the interest to whole year periods. I know how to do it in a very un-compact way. Where I am falling down is composing the formula to compound interest daily without having a cell for each day (which is completely ridiculously bloated method, obviously).

So, if someone could show me how to do this for two example periods, at two example rates, I can then apply that example to my multiple [8] periods, each taking a different interest rate.

Maybe $100,000. at 7% per annum, compounded daily, for all of 2018, and then 6% per annum, compounded daily, for all of 2019? Thanks very much in advance.--108.21.51.204 (talk) 15:55, 15 November 2020 (UTC)

Unless I misunderstand something, if you have an interest rate of 7% per annum, or a factor of 1.07 to be paid back applied to the principal amount given in loan, then the equivalent per diem factor, for a year of 365 days, equals 1.07^1/₃₆₅, or about 1.000185 (0.0185%). The result of compounding this daily for a period of a year is then (1.07^1/₃₆₅)³⁶⁵ = 1.07^{(1/₃₆₅×365)} = 1.07. If you do not want to see the compounded sum on a day-to-day basis, why take such a roundabout way to show that 1.07 = 1.07?  --Lambiam 16:49, 15 November 2020 (UTC)

You seem to be saying that one year of compounding daily interest will equal the same as $1.00 x 0.07 = $1.07 — that's only true if you're rounding to two decimal places for some sort of academic exercise. I'm not using one dollar, and this isn't some technical exercise for kicks. The actual underlying issue has hundreds of thousands of dollars involved. When you compound interest every day, on day two your principal is higher, i.e., on day one you're multiplying $1.00 by 0.07, dividing that number by 365, and coming up with interest of $0.00019178082.... On day two your principal is not $1.00 it's $1.000191781082... and you're dividing that number by 365 and coming up with interest of $0.00019181760..., and so on for 365 days.

And if you go to an online compounding interest calculator, insert $10K and choose daily, for a period of one year, you end up with $10,725.01 as the total, versus $10,700.00 is you choose annual interest. For my purpose, I can't just present totals and say I used an online calculator. I need to SHOW the calculation in a spreadsheet for a particular principal over a slightly longer than eighteen year designated period, with the interest rate changing at eight distinct, known date points.--108.21.51.204 (talk) 17:57, 15 November 2020 (UTC)

Maybe this will help clarify my question (for spoonfeeding me). If I enter:

$100,000 in Cell A1,

and in Cell B1 I want to apply a formula to find what the total amount of interest is on the amount in Cell A1, compounded daily for 622 days, at 7.3% interest

so that I can, in turn, enter into Cell A2: =SUM(A1+B1);

the formula I would type into Cell B1 would be: =SUM(A1...????)--108.21.51.204 (talk) 18:41, 15 November 2020 (UTC)

What you want is (1+p/365)^n, where p is interest rate and n is number of days. For 7.3% interest that's (1+0.073/365)^622 = 1.0002^622 ~ 1.1325. Remove 1 (which is the principal) and you have 0.1325 or 13.25% annualized. A reasonably good estimate (off by a dollar per $100,000 in your particular example) is e^(0.073*622/365), that is continuous compounding (in Excel e^x is EXP(x)). However if you're quite literally compounding the interest day by day and rounding to the nearest cent, then there will be significant rounding errors which will magnify over time, and the true result cannot be prognosticated by the usual formula. I suppose you could multiply by 1.0002 and have Excel round it in some particular fashion, but it's best to leave that to someone certified for this job. 93.136.22.169 (talk) 19:31, 15 November 2020 (UTC)

(ec) So you are saying the per diem interest is not 0.0185% but (0.7/365)% = ca. 0.0191% So if the compounding is not done daily but weekly, do you pay back $10,724.58? And if monthly, only $10,722.90?

Then in general, if the nominal percentage for the whole period is $P\%$, and the number of installments is $N$, the original sum needs to be multiplied by a factor of

$\left(1+{\frac {P}{100N}}\right)^{N}.$

So, for example,

$\left(1+{\frac {7}{100\times 365}}\right)^{365}=1.072501~.$

I am not familiar with Excel, including its syntax for maths formulas, but I suspect something like (1+P/(100*N))^N will work, where you replace the variables P and N by numbers or the names of spreadsheet cells holding the values.  --Lambiam 19:54, 15 November 2020 (UTC)

Specialized to the concrete question: use =A1*(1+7.3/(100*622))^622 for cell A2. The resulting number should be (rounded to two decimals) 10,757.26. Or if you want to use =SUM(A1+B1) with an explicit result in cell B1, =A1*(1+7.3/(100*622))^622-A1.  --Lambiam 13:43, 16 November 2020 (UTC)

However, if the 7.3% interest is specified as being per annum, then =A1*(1+7.3/(100*365))^622 as proposed above by 93.136.22.169 is more likely. (There is something perverse in citing an annual rate as 7.3% if the actual annual rate is 7.57%.)  --Lambiam 14:01, 16 November 2020 (UTC)

Excel has several financial functions built in - I'm not an expert on these, but possibly the ACCRINT function may do all this for you? You may get more help at the computing desk, or at a specific Excel forum such as MrExcel. — O Still Small Voice of Clam 07:43, 16 November 2020 (UTC)