Topic Wiki

Rule 1: Did you try A.I? Right before you click post, give it a quick run. Most excel questions can be answered very quickly, thoroughly, and clearly via chatgpt. Wrong answer, or having trouble with the correct wording of the prompt? Post for help

=================
For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by Jojo202 on March 01, 2024, 12:09:44 AM »

Author Topic: Excel Help and Problems  (Read 297368 times)

Offline Centurion

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2012
  • Posts: 1310
  • Total likes: 5
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York / Jerusalem
  • Programs: spg plat,avis pres ,hertz plat ,AAexcutime, delta plat,hilton gold, united
Re: Excel Help and Problems
« Reply #1220 on: January 20, 2019, 02:59:15 PM »
If I'm understanding you correctly, the math is overhead times 12 divided by the interest rate.
So 20k x12 is 240k, divided by 7% is 3,428,571 in loans needed.
so if I start with 3.5 i'm good to go?
you can only make a first impression ONCE

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7647
  • Total likes: 301
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1221 on: January 20, 2019, 03:59:15 PM »
I can use help figuring out how much money in mortgage loans we need to cover our overhead.
how many months and how many dollars needed to cover ourselves

when will the payments be able to match the loans we give out to be self sustained

i.e. if we give out 1m in loans @7%  we would only make x per year /12 is y per month
if our overhead is 20k a month - how many loans need to be out etc.

willing to pay - feel free to dm
Start with this
https://www.calculator.net/loan-calculator.html

Just plug in you numbers and look at the amortization table.
To get 20k+ of interest income @7% you would need to have 4mm out all the time. Also need to make sure you are turning these over so all p returned is lent out again.

This assumes a zero default rate. - obviously wrong.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2634
  • Total likes: 53
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1222 on: January 20, 2019, 05:11:31 PM »
Start with this
https://www.calculator.net/loan-calculator.html

Just plug in you numbers and look at the amortization table.
To get 20k+ of interest income @7% you would need to have 4mm out all the time. Also need to make sure you are turning these over so all p returned is lent out again.

This assumes a zero default rate. - obviously wrong.
How do you get 4mm, the loans are probably interest only so the interest payment will be stable?

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7647
  • Total likes: 301
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1223 on: January 20, 2019, 05:14:17 PM »
How do you get 4mm, the loans are probably interest only so the interest payment will be stable?
.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2634
  • Total likes: 53
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1224 on: January 20, 2019, 05:16:54 PM »
so if I start with 3.5 i'm good to go?
You need to understand the math before you give people millions of dollars.
If the loan is "interest only" then 7% means that for every dollar you lend, the borrower will owe you 7 cents per year. So  if you lend out 3.5mm, the borrower will owe you a yearly amount of 3.5mm times 7% (3,500,000 x .07) which is 245k. If you split that between 12 months, you get 20,416 a month.

There are usually fees that borrowers charge with these types of loans that can generate additional income.
« Last Edit: January 20, 2019, 05:21:59 PM by yitzf »

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2634
  • Total likes: 53
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1225 on: January 20, 2019, 05:19:18 PM »
.
That's with principal payments reducing the balance. I would think that these types of loans are typically interest only.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7647
  • Total likes: 301
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1226 on: January 20, 2019, 05:19:56 PM »
That's with principal payments reducing the balance. I would think that these types of loans are typically interest only.
Oh.. no idea he called it a mortgage..

Offline Am

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2011
  • Posts: 1002
  • Total likes: 14
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1227 on: January 24, 2019, 05:56:36 PM »
How can I do this?
I need to create a column where every cell should have a date one month later than the previous. My particular case needs 120 months. So I can enter Jan. 1, 2019 in cell A1 and it should automatically fill in Feb. 1, 2019 in cell A2 and March 1, 2019 in cell A3 and so on for 120 rows.

Thanks.

Offline Zubda

  • DansDeals Copper Elite
  • *
  • Join Date: Apr 2018
  • Posts: 19
  • Total likes: 4
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1228 on: January 24, 2019, 06:01:51 PM »
How can I do this?
I need to create a column where every cell should have a date one month later than the previous. My particular case needs 120 months. So I can enter Jan. 1, 2019 in cell A1 and it should automatically fill in Feb. 1, 2019 in cell A2 and March 1, 2019 in cell A3 and so on for 120 rows.

Thanks.
Enter this formula in cell A2 `=DATE(YEAR(A1), MONTH (A1) +1, DAY(A1))`
Abd drag it down

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1229 on: January 24, 2019, 06:33:46 PM »
Enter this formula in cell A2 `=DATE(YEAR(A1), MONTH (A1) +1, DAY(A1))`
Abd drag it down
Just use =EDATE(A1,1) and copy down

Offline Centurion

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2012
  • Posts: 1310
  • Total likes: 5
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York / Jerusalem
  • Programs: spg plat,avis pres ,hertz plat ,AAexcutime, delta plat,hilton gold, united
Re: Excel Help and Problems
« Reply #1230 on: January 24, 2019, 09:58:26 PM »
How can I do this?
I need to create a column where every cell should have a date one month later than the previous. My particular case needs 120 months. So I can enter Jan. 1, 2019 in cell A1 and it should automatically fill in Feb. 1, 2019 in cell A2 and March 1, 2019 in cell A3 and so on for 120 rows.

Thanks.

type 01/01/2019 in first cell, then 02/01/2019 in second cell, then drag
you can only make a first impression ONCE

Offline Zubda

  • DansDeals Copper Elite
  • *
  • Join Date: Apr 2018
  • Posts: 19
  • Total likes: 4
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1231 on: January 24, 2019, 10:21:15 PM »
type 01/01/2019 in first cell, then 02/01/2019 in second cell, then drag
Lol, sometimes you only overthink and don't think of the simple ways

Offline ludmila

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Aug 2012
  • Posts: 5155
  • Total likes: 271
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: IND
  • Programs: Hilton Diamond,Marriott Platinum,SPG Platinum.
Re: Excel Help and Problems
« Reply #1232 on: January 24, 2019, 10:24:05 PM »
Lol, sometimes you only overthink and don't think of the simple ways
+1 As we all do in most cases :)
I was the Best,still the Best, and will always be the Best.
Pele Good,Maradona Better, George Best.

Offline metsguy

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Apr 2011
  • Posts: 366
  • Total likes: 17
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1233 on: January 24, 2019, 10:57:29 PM »
+1 As we all do in most cases :)

As an excel Snob I would never consider a solution that involves a mouse.
Edate or Eomonth(A1,0)+1  FTW

Offline Zubda

  • DansDeals Copper Elite
  • *
  • Join Date: Apr 2018
  • Posts: 19
  • Total likes: 4
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1234 on: January 24, 2019, 11:00:45 PM »
As an excel Snob I would never consider a solution that involves a mouse.
Edate or Eomonth(A1,0)+1  FTW
Still requires a mouse (to drag down...)

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5386
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Help and Problems
« Reply #1235 on: January 24, 2019, 11:02:13 PM »
Still requires a mouse (to drag down...)
Not if you know how to use your keyboard properly.

Offline Zubda

  • DansDeals Copper Elite
  • *
  • Join Date: Apr 2018
  • Posts: 19
  • Total likes: 4
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1236 on: January 24, 2019, 11:07:31 PM »
Not if you know how to use your keyboard properly.
Just tested it, and you've got a point!

Offline metsguy

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Apr 2011
  • Posts: 366
  • Total likes: 17
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1237 on: January 24, 2019, 11:14:12 PM »
Not if you know how to use your keyboard properly.

This.

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5386
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Help and Problems
« Reply #1238 on: February 13, 2019, 08:20:30 PM »
Been trying to figure out a simple way to have a variable number (1-9) as part of an IF statement. Maybe someone here can help.

Text that Iím looking for the IF to match can be XYZ1, XYZ2, XYZ3 etc. There will always be an XYZ followed by 1-9 that I need it to pick up.
Currently I have it set to IF XYZ & cell reference (where I put in the 1-9 that it is), and the rest of my formulas work off that. I want to cut out the manually updating the cell reference each time I need my logic to populate.

Use case is in google sheets if that opens up more options.

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5121
  • Total likes: 2238
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1239 on: February 13, 2019, 08:40:08 PM »
Been trying to figure out a simple way to have a variable number (1-9) as part of an IF statement. Maybe someone here can help.

Text that Iím looking for the IF to match can be XYZ1, XYZ2, XYZ3 etc. There will always be an XYZ followed by 1-9 that I need it to pick up.
Currently I have it set to IF XYZ & cell reference (where I put in the 1-9 that it is), and the rest of my formulas work off that. I want to cut out the manually updating the cell reference each time I need my logic to populate.

Use case is in google sheets if that opens up more options.
IF(Left(A2,3)="XYZ",1,2)?
["-"]