Mathematics desk
< July 12	<< Jun \| July \| Aug >>	July 14 >

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.

July 13

Microsoft Excel formula to calculate interest

I am not sure if I should post this here at the Math Reference Desk or at the Computers Reference Desk. I would like to calculate the interest that is owed to me by another person, and I would like to create an Excel spreadsheet to do the calculations. (I have to do several of these, not just one. Which is why I want to do it all through an Excel spreadsheet.)

So, here is some basic information (just as an example). The person owes me $378.29. This amount was due to me on April 15, 2016. The outstanding balance due to me (of $378.29) is subjected to an annual interest rate of 13%. The interest is compounded daily. So, is there some Excel formula I can employ that will calculate how much interest this person owes me? Thanks. Joseph A. Spadaro (talk) 05:37, 13 July 2017 (UTC)[reply]

I would set up a very simple spreadsheet with columns: A:Date of loan; B:Amount of loan; C:Interest rate; D:Current value

Each row below will be a separate loan, for example on row 2 you will have April 15, 2016 in cell A2; $378.29 in cell B2; 0.13 in cell B3.

The formula for cell D2 will be "=B2*(1+C2/365)^(TODAY()-A2)" where the last bit after ^ counts the number of days since the loan. This gives a current value of $444.67 (as of July 13th.) You can replicate the formula to as many rows as you need.

My simple version might need some tweaking for leap years (though the Excel function TODAY minus date copes with the extra day). I might possibly have misinterpreted your 13% because I've taken it to mean 13/365% per day. Dbfirs 06:53, 13 July 2017 (UTC)[reply]

Thanks. As far as the interest rate, my above posting stated: "an annual interest rate of 13%" (i.e., 0.13 as a decimal). So, yes, I assume that means that the daily interest rate is 0.13 divided by 365, which yields 0.035616. So, another question for you: are you sure that your formula yields compounded interest? And that the interest is compounded daily? Also, I want my "final answer" to be the amount of interest owed to me (not the total amount of money owed to me). Thanks. Joseph A. Spadaro (talk) 14:27, 13 July 2017 (UTC)[reply]

Yes, his formula includes compounding: he divides by 365 to get the daily interest rate, adds 1 to get the daily growth factor, and raises the the power (number of elapsed days) to implement daily compounding. See Compound interest#Calculation of compound interest. As for the interest owed, just subtract the original principal $378.29 from the amount owed. Loraof (talk) 15:33, 13 July 2017 (UTC)[reply]

Thanks. Joseph A. Spadaro (talk) 15:52, 13 July 2017 (UTC)[reply]

As more of a "black box" alternative you could use Excel's FV() (Future Value) function - see here for details and examples. AndrewWTaylor (talk) 07:57, 13 July 2017 (UTC)[reply]

Thanks. I tried those (or similar). But, I always get screwed up with the period of time ("days" versus "years", etc.). Joseph A. Spadaro (talk) 14:28, 13 July 2017 (UTC)[reply]

Calculating the number of days using Excel is pretty simple. Just use a formula to subtract the

later date from the earlier date. For example, if cell A1 contains 1-Jan-2004 and cell A2

contains 03-Mar-2004, you simply enter the formula =A2-A1 in cell A3 to get the number of days.

Step 1. calc the number of days

Step 2. Calc interest

Interest = ( (1+r/n)^days - 1) * P

Where

P = 378.29

n = 365

r = 0.13

days = "13-Jul-2017" - "15-Apr-2016"

110.22.20.252 (talk) 00:01, 14 July 2017 (UTC)[reply]

Thanks. Why does your formula "minus 1" from "days"? Thanks. Joseph A. Spadaro (talk) 03:24, 14 July 2017 (UTC)[reply]

Because you are only interested in the Interest. We have

NewP = (1+r/n)^days * P

Interest = NewP - P

Interest = (1+r/n)^days * P - P

Interest = P * ( (1+r/n)^days - 1 )

Interest = ( (1+r/n)^days - 1 ) * P

110.22.20.252 (talk) 05:18, 14 July 2017 (UTC)[reply]

For interest only, just subtract the amount of the loan, so my formula for cell D2 would read D2 will be "=B2*(1+C2/365)^(TODAY()-A2)-B2". (This is the same as the above formula, but might be easier for you to understand.) Dbfirs 06:10, 14 July 2017 (UTC)[reply]

Fourier Sine Transform of PDE with Heaviside Step Function as Initial Condition

I'm working my way through a textbook on PDEs, and I'm stuck on the Fourier sine transform. Here is the PDE to transform:

${\begin{aligned}u_{t}&=\alpha ^{2}u_{xx}\\u_{x}(0,t)&=0\\u(x,0)&=H(1-x)\\\end{aligned}}$

where ${\textstyle H(x)}$ is the Heaviside step function. So, a standard heat equation with an interesting initial condition. For the transformed equation, I get:

$U'(t)=\alpha ^{2}\lbrack -\omega ^{2}U(t)+{\frac {2C\omega }{\pi }}\rbrack$

Note that I substituted ${\textstyle C}$ for the boundary condition, since the derivative of a constant is zero. When I try and transform the initial condition, I get the following integrals:

$F_{s}[u(x,0)]={\begin{cases}{\frac {2}{\pi }}\int _{0}^{0}\ (1)\sin(\omega t)\,dt,&x=0\\{\frac {2}{\pi }}\int _{0}^{\infty }\ (0)\sin(\omega t)\,dt,&x>0\\\end{cases}}$

Both of these evaluate to zero, so I thought the initial condition ${\textstyle u(x,0)}$ was zero; however, that gives me the following ODE:

${\begin{aligned}U'(t)+\omega ^{2}\alpha ^{2}U(t)&={\frac {2C\omega \alpha ^{2}}{\pi }}\\U(0)&=0\\\end{aligned}}$

The homogeneous solution is ${\textstyle U(t)=Ae^{-\omega ^{2}\alpha ^{2}t}}$ , but plugging in the initial condition leaves ${\textstyle A=0}$ , which zeroes out the solution. Any help is appreciated. OldTimeNESter (talk) 18:37, 13 July 2017 (UTC)[reply]

What domain are you working on?--Jasper Deng (talk) 16:13, 14 July 2017 (UTC)[reply]

It is far easier to solve it using Laplace transform by time. I attempted but the problem has no solutions if

x\in [-\infty ,\infty ]

. Ruslik_Zero 20:36, 14 July 2017 (UTC)[reply]

But a solution exists in the more logical

x\in [0,\infty ]

domain. Ruslik_Zero 20:52, 14 July 2017 (UTC)[reply]

I'm not sure what you mean by domain (I'm new to this): doesn't the Fourier transform change from the time domain to the frequency domain? OldTimeNESter (talk) 21:53, 14 July 2017 (UTC)[reply]

What subset of

\mathbb {R} ^{2}

do you want your solution to be valid on?--Jasper Deng (talk) 06:32, 15 July 2017 (UTC)[reply]

I think the domain is

x\geq 0,t\geq 0

. Ruslik_Zero 08:58, 15 July 2017 (UTC)[reply]

sorry, brain fart. Yes, it is

x\geq 0,t\geq 0

. OldTimeNESter (talk) 14:06, 15 July 2017 (UTC)[reply]