Solved

Simple Excel Formula Help..  Daily interested Rate of  .008582

Posted on 2008-06-18
13
304 Views
Last Modified: 2010-04-03
I need help with Excel formula... I want to make a spreadsheet that goes for at least 5 years on a daily basis
with a daily interest rate of  .08528%.    I would like to have a spread sheet that has this formula.

How do I make this as I am a beginner in  Excel.?

Thank you,
0
Comment
Question by:4ubest
  • 7
  • 6
13 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 21817283
assuming you have a value in A1, the following formula will give you the value+compounded interest over 5 years.
=A1*(1+0.08528%)^(365*5+1)
Does that answer your question?

Thomas
0
 

Author Comment

by:4ubest
ID: 21817524
I think I messed up with my question...   Instead of 1% interest per day it is .85228%  per day.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 21817570
Do you want to calculate the total interest, the daily interest (it would vary daily due the compound effect) or the total at the end of 5 years?
My formula does take the 0.08528% per day, I'm adding 1 to calculate the compound effect, e.g. $100+ 5%=$100*(1+5%), i.e. 105$, not just 5$
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:4ubest
ID: 21817768
You are the expert..  doing it longhand and using 1%/day  I got starting with $7,000 after 30 days  $9300 and after 60 days  $12346  and after 120 days   $21714   but I now need to calculate all these at  .8528%/day

0
 
LVL 39

Expert Comment

by:nutsch
ID: 21817942
Try that
in cell A1, put .8528%
in cell A2, put your number of days =365*5
in cell A3, put your amount $7000
in cell A4, put your result =A3*(1+A1)^A2
That way you can play with the different variables in cells A1 to A3

Warning, this is compound interest, meaning the interest gained in day 1 earns interest on day 2.
The simple interest formula is the following :
=A3*(1+A1*A2)
0
 

Author Comment

by:4ubest
ID: 21818058
Hi.,.. I never felt so thick before... What I want to do is to put  X dollars in on Day 1 and at  .8528% interest to be able to see   the total for each day and the interest earned that day  for about 3700 days.

This is what I have started but I do not yet know how to carry the formula down the columns. so the computer does it automatically and not me.

http://screencast.com/t/i8c0Sc0sg
0
 
LVL 39

Expert Comment

by:nutsch
ID: 21818090
take your excel file, go to cell a3701, click on cell A3701, do Ctrl+shift+Up Arrow, Tab, F2, Ctrl+Enter
Repeat from cell B3701. That should take care of copying the formulas.
0
 

Author Comment

by:4ubest
ID: 21818174
This is what I get after the first step..


go to cell a3701, click on cell A3701, do Ctrl+shift+Up Arrow, Tab, F2, Ctrl+Enter


http://screencast.com/t/odQQNEDg


doing that for B3701  I made a huge mess:  I do not think I did this as you said but I tried  3x and got the same thing...

http://screencast.com/t/IipTBzKNBT





0
 
LVL 39

Expert Comment

by:nutsch
ID: 21818198
change your formula in cell B6 and replace B1 by $B$1, thus changing your relative address to an absolute address, then redo the copy.
0
 

Author Comment

by:4ubest
ID: 21818405
It has to be easier than this as I had a lady do it while we were on the phone a week ago and that is what I used until I tried to modify it from 1%/day to   .8528%/day  and ruined the spreadsheet.

What you are doing is too hard for this old brain to understand,
0
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 21818433
well I assume that your formula in cell B2 is something like =A2*B1, isn't it? If you copy it down to the next cell, by one of the below means:
- Copy / Paste
- Ctrl + C / Ctrl + V
- get your pointer on the lower right corner of the cell until it turns into a little black cross and drag down
you will have the formula A3*B2  in cell B3. But that's not what you want. what you want is A3*B1. By changing the formula to A3*$B$1, when you copy it down, it will become A4*$B$1, A5*$B$1, etc.
Clear as mud?
0
 

Author Comment

by:4ubest
ID: 21818523
That worked...  thank you.  
0
 
LVL 39

Expert Comment

by:nutsch
ID: 21818547
Thanks for the grade, sorry if I took a while to understand your problem.

Thomas
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to update Firmware and Bios in Dell Equalogic PS6000 Arrays and Hard Disks firmware update.
Concerto Cloud Services, a provider of fully managed private, public and hybrid cloud solutions, announced today it was named to the 20 Coolest Cloud Infrastructure Vendors Of The 2017 Cloud  (http://www.concertocloud.com/about/in-the-news/2017/02/0…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question